Глава 8. Работа с запросами

Для формирования и выполнения запросов к таблицам базы данных в системе используется специальный объект Запрос. Запрос удобно использовать, когда необходимо получить сложную выборку данных, сгруппированную и отсортированную нужным образом. Одним из классических примеров его применения может служить сводка по состоянию регистра учета на определенный момент времени. Кроме того, механизм запросов позволяет легко получать информацию в различных временных разрезах.

В качестве источников данных языка запросов выступают таблицы базы данных. Таблицы подразделяются на два основных класса: реальные и виртуальные.

Реальные таблицы хранятся в базе данных, то есть интерпретируются из реально существующей таблицы базы данных. В случае использования реальной таблицы могут присутствовать вычисляемые поля, значения которых вычисляются как функция нескольких реальных полей.

Виртуальные таблицы не хранятся в базе данных. При обращении к информации виртуальных таблиц система автоматически собирает информацию реальных таблиц базы данных для выполнения запроса. Виртуальная таблица может быть параметризована, то есть реальное наполнение виртуальной таблицы может определяться значениями параметров, фактические значения которых задаются в тексте запроса. Для каждой виртуальной таблицы определяется имя, которое используется в запросах для идентификации таблицы.

Имя таблицы может быть задано на английском и русском языках. Например, Справочник.Товары. Имена таблиц и полей не могут совпадать с ключевыми словами языка запросов.

Отдельный подкласс таблиц образуют так называемые объектные таблицы. В качестве объектной таблицы обязательно выступает реальная таблица базы данных. Смысловое отличие объектных таблиц от прочих следует из названия – объектные таблицы предназначены для хранения состояния объектов системы «1С:Предприятие», таких как справочники, документы и т. п. Каждой объектной таблице соответствует тип объектов системы «1С:Предприятие». Например, объектам типа Справочник.Товары соответствует одна таблица, объектам типа Справочник.Контрагенты – другая. Каждая отдельная запись объектной таблицы хранит состояние отдельного объекта соответствующего типа. В соответствии с этим у каждой объектной таблицы определено поле типа Cсылка на текущую запись. Кроме того, для объектных таблиц определен способ получения пользовательского представления объекта из содержимого полей записи.

Объектные таблицы могут быть также иерархическими. Для иерархических таблиц определяется специально выделенное поле Родитель, содержащее ссылку на запись, которой в соответствии с иерархией подчиняется текущая запись.

В качестве поля таблицы может фигурировать поле таблицы или вложенная таблица. Основное отличие обычного поля от вложенной таблицы состоит в том, что в рамках одной записи обычному полю соответствует одно-единственное значение, а вложенной таблице соответствует значение типа РезультатЗапроса с заранее заданным набором колонок. Примером вложенной таблицы может являться табличная часть документа или справочника.

В качестве типов значения полей таблиц может выступать значение типа NULL. Такие значения содержатся в полях записей таблиц, для которых данное поле не определено или не имеет смысла. Например, значения такого типа содержатся в записях, относящихся к группам справочника, по полям, для которых установлено, что они могут иметь значение только у элементов этого справочника.

Обычное поле – поле, которое формируется из данных, которые хранятся в СУБД, при этом данные могут быть дополнительно обработаны средствами СУБД. Виртуальное поле – поле, которое формируется из данных, которые хранятся в СУБД, но данные дополнительно обработаны средствами сервера «1С:Предприятия» (в том числе в файловом варианте). Следующие поля в таблицах запросов являются виртуальными:

● Поля представлений, полученных как с помощью поля Представление соответствующей таблицы, так и с помощью функции языка запросов.

● ВерсияДанных;

● ИмяПредопределенныхДанных;

● МоментВремени;

● ТипЗначения таблицы плана видов характеристик;

● Вид таблицы плана счетов;

● Тип таблицы журнала документов;

● ВидДвижения таблицы регистра бухгалтерии;

● ВидДвижения таблицы регистра накопления;

● любые поля со значениями следующих типов: ХранилищеЗначения, УникальныйИдентификатор и ссылка внешнего источника данных.

Виртуальные поля имеют следующие особенности:

● Не все виртуальные поля можно преобразовать «обратно» в значение, хранящееся в СУБД. Такими полями являются поля представления и поле ТипЗначения для таблицы плана видов характеристик.

● Не все виртуальные поля с одинаковым наименованием можно сравнивать между собой, если они получены из различных таблиц. Такими полями являются ВерсияДанных, ИмяПредопределенныхДанных, МоментВремени, Тип таблицы журнала документов.

● Если в поле запроса получается значение системного перечисления (ссылки на таблицу внешних источников данных или уникального идентификатора), и полученное значение не комбинируется с другими типами, то при использовании в запросе операции СГРУППИРОВАТЬ ПО значения этого поля группируются вне зависимости, от того, из каких таблиц получается значение.

● Для виртуальных полей строкового типа не поддерживается использование операций работы со строками и сравнение с данными типа Строка.

Как было описано выше, для выполнения запроса необходимо составить текст запроса. Текст запроса – это инструкция, в соответствии с которой должен быть выполнен запрос. В тексте запроса описывается, какие таблицы информационной базы используются в качестве источников данных запроса, поля таблиц, которые требуется обрабатывать в запросе, правила группировки, сортировки результатов и т. д.

Инструкция составляется на специальном языке (языке запросов) и состоит из отдельных частей – секций, предложений, слов, функций и комментариев. Далее в этой главе рассматривается назначение и способы использования всех конструкций языка запросов.

В данной главе синтаксис языка запросов описывается при помощи набора правил. Каждое правило описывает одну конструкцию языка.

Каждая конструкция языка может содержать в себе ключевые слова; разделители (точки, запятые, круглые скобки), в свою очередь, другие конструкции языка.

<Конструкция языка>

ЭТО_КЛЮЧЕВОЕ_СЛОВО
<Это_конструкция_языка>, <Это_конструкция_языка>
ЭТО_ФУНКЦИЯ ( <Это_конструкция_языка> )

В правилах, описывающих язык запросов, конструкции языка указываются в угловых скобках. Ключевые слова и названия функций описываются заглавными буквами.

Конструкции языка могут содержать необязательные элементы – ключевые слова и пр. В правилах, описывающих язык запросов, необязательные элементы заключены в квадратные скобки.

[ЭТО_НЕОБЯЗАТЕЛЬНОЕ_СЛОВО]
[<Это_необязательная_конструкция>]

В некоторых случаях в конструкции языка может использоваться один из нескольких альтернативных элементов. Такие элементы в правилах перечисляются через вертикальную черту.

ЛИБО_ЭТО_СЛОВО | ЛИБО_ЭТО_СЛОВО
<Либо_эта_конструкция> | <Либо_эта_конструкция>

Описания всех конструкций сопровождаются примерами, поясняющими порядок их использования в языке запросов.

Текст запроса может включать комментарии. Комментарием считается часть строки, начинающаяся с последовательности символов // и продолжающаяся до конца строки.

// Это комментарий

При выполнении запроса комментарии игнорируются.

Примечание. Конструктор запросов удаляет комментарии из текста.

Текст запроса может содержать предопределенные данные конфигурации, такие как:

● значения перечислений,

● предопределенные данные:

● справочников,

● планов видов характеристик,

● планов счетов,

● планов видов расчетов,

● пустые ссылки,

● значения точек маршрута бизнес-процессов.

Также текст запроса может содержать значения системных перечислений, которые могут быть присвоены полям в таблицах базы данных: ВидДвиженияНакопления, ВидСчета и ВидДвиженияБухгалтерии.

Обращение в запросах к предопределенным данным конфигурации и значениям системных перечислений осуществляется с помощью литерала функционального типа.

ЗНАЧЕНИЕ(<ПредставлениеЗначения>)

Для системных перечислений представление значения имеет вид:

<ИмяСистемногоПеречисления>.<Значение>

Допустимые имена системных перечислений приведены выше, с перечнем допустимых для каждого из них значений можно ознакомиться в его описании.

Для предопределенных данных конфигурации представление значения имеет вид:

<ТипПредопределенногоЗначения>.<ИмяОбъектаМетаданных>.<Значение>

Тип предопределенного значения может быть:

● Справочник (Catalog);

● ПланВидовХарактеристик (ChartOfCharacteristicTypes);

● ПланСчетов (ChartOfAccounts);

● ПланВидовРасчета (ChartOfCalculationTypes);

● Перечисление (Enum).

В качестве имени объекта метаданных указывается имя объекта метаданных, как оно задано в конфигураторе.

Для определенных в конфигурации перечислений значение указывается как имя соответствующего объекта метаданных типа ЗначениеПеречисления. Для всех остальных типов предопределенных значений – как имя предопределенного элемента данных, как оно указано в конфигураторе, или ПустаяСсылка (EmptyRef) для указания пустой ссылки.

Для точек маршрутов бизнес-процессов представление значения имеет вид:

БизнесПроцесс.<ИмяОбъектаМетаданных>.ТочкаМаршрута.<ИмяТочкиМаршрута>

Ниже приведено несколько фрагментов запросов, поясняющих использование предопределенных данных в запросах.

ГДЕ Город = ЗНАЧЕНИЕ(Справочник.Города.Москва)
ГДЕ Город = ЗНАЧЕНИЕ(Справочник.Города.ПустаяСсылка)
ГДЕ ТипТовара = ЗНАЧЕНИЕ(Перечисление.ВидыТоваров.Услуга)
ГДЕ ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
ГДЕ ТочкаМаршрута = ЗНАЧЕНИЕ(БизнесПроцесс.Согласование.ТочкаМаршрута.Согласие)

Одной из существенных особенностей языка запросов системы «1С:Предприятие» является то, что, как и во встроенном языке, все ключевые слова имеют два варианта написания: на русском и английском языках. Далее в этой главе указываются русские варианты написания ключевых слов. Ниже в таблице приведены соответствия русского и английского вариантов написания ключевых слов языка запросов.

Примечание. Регистр букв (строчные или заглавные) при написании не имеет значения.

Текст запроса можно описать следующим правилом:

<Текст Запроса>

<Описание запроса>
[<Объединение запросов>]
[<Упорядочивание результатов>]
[АВТОУПОРЯДОЧИВАНИЕ]
[<Описание итогов>]

Как видно из этого правила, текст запроса состоит из нескольких частей, или секций:

● <Описание запроса> – это единственная обязательная секция в тексте запроса, и во многих случаях достаточно указать только ее. В секции определяются источники данных запроса, поля выборки, группировки и т. д. Эта секция, в свою очередь, описывается целым набором правил и подробно рассматривается ниже.

● <Объединение запросов> – язык запросов позволяет объединять результаты выполнения нескольких запросов. Описание объединения запросов см. здесь.

● В секции <Упорядочивание результатов> можно определить условия упорядочивания строк в результате запроса. Описание упорядочивания результата запроса см. здесь.

● Секция АВТОУПОРЯДОЧИВАНИЕ позволяет включить режим автоматического упорядочивания строк в результате запроса. Описание данного режима см. здесь.

● В секции <Описание итогов> можно указать, какие итоги необходимо рассчитывать в запросе. Описание данной секции см. здесь.

Как уже было сказано, в тексте запроса должна обязательно присутствовать секция описания запроса, в которой определяются:

● поля, которые будут содержаться в результате запроса;

● источники данных запроса – исходные таблицы;

● условия, влияющие на выборку данных в запросе;

● порядок группировки результатов запроса.

Секция описания запроса состоит из нескольких взаимосвязанных предложений:

<Описание запроса>

ВЫБРАТЬ [РАЗРЕШЕННЫЕ] [РАЗЛИЧНЫЕ] [ПЕРВЫЕ <Количество>]
    <Список полей выборки>
[ИЗ <Список источников>]
[ГДЕ <Условие отбора>]
[СГРУППИРОВАТЬ ПО <Поля группировки>]
[ИМЕЮЩИЕ <Условие отбора>]
[ДЛЯ ИЗМЕНЕНИЯ [<Список таблиц верхнего уровня>]]

Описание запроса начинается с обязательного ключевого слова ВЫБРАТЬ.

Ключевое слово РАЗРЕШЕННЫЕ означает, что запрос выберет только те записи, на которые у текущего пользователя есть права. Если данное слово не указать, то запрос отработает с ошибкой, когда он выберет записи, на которые у пользователя нет прав. Данное ключевое слово может присутствовать только в предложении ВЫБРАТЬ верхнего уровня и распространяется на весь запрос, включая вложенные запросы. Следует учитывать, что использование ключевого слова РАЗРЕШЕННЫЕ оказывает влияние на работу только в том случае, если на таблицы наложены ограничения доступа к данным (см. здесь). Права доступа на саму таблицу не учитываются при использовании ключевого слова РАЗРЕШЕННЫЕ. Например, если на таблицу отсутствует право ЧТЕНИЕ, то запрос с использованием такой таблицы, будет выполнен с ошибкой вне зависимости от использования в запросе ключевого слова РАЗРЕШЕННЫЕ.

С помощью ключевого слова РАЗЛИЧНЫЕ можно указать, что в результат не должны попадать повторяющиеся строки.

Конструкция ПЕРВЫЕ <Количество> позволяет задать предельное количество строк в результате запроса. Будут отобраны самые первые (в соответствии с правилами упорядочивания результатов запроса) строки. Количество задается целым числом. В языке запросов добавлена возможность исполнения упорядочивания во вложенных запросах в случае, если вложенный запрос содержит конструкцию ПЕРВЫЕ.

В секции <Список полей выборки> описываются поля, которые должны содержаться в результате запроса (правила описания полей выборки см. здесь).

В предложении ИЗ <Список источников> указываются источники данных – таблицы информационной базы, содержимое которых обрабатывается в запросе. Описание источников может быть опущено только в том случае, если они полностью определены в списке полей выборки (правила описания источников данных запроса см. здесь).

Предложение ГДЕ <Условие отбора> позволяет отфильтровать результат запроса. В результат попадают только те записи, для которых указанное условие оказывается истинным (правила описания условий отбора см. здесь).

Предложение СГРУППИРОВАТЬ позволяет описать порядок группировки результатов запроса (см. здесь).

Предложение ИМЕЮЩИЕ позволяет накладывать условия на результаты группировки (см. здесь).

Предложение ДЛЯ ИЗМЕНЕНИЯ предназначено для указания необходимости блокировки считываемых в транзакции данных. Считанные данные становятся недоступными для чтения в других сессиях. Для файлового варианта блокируются указанные таблицы, а для клиент-серверного варианта – только выбранные записи. Блокировка снимается после завершения транзакции.

Во всех примерах запросов в данной главе приводится текст запроса и результат запроса. Подразумевается, что текст запроса передается в качестве параметра методу Выполнить() объекта Запрос.

Приведем пример достаточно простого запроса, состоящего из одного оператора ВЫБРАТЬ и списка полей выборки.

Пример:

// В отчет необходимо вывести список товарных накладных.
Выбрать
Документ.РасходнаяНакладная.Ссылка

Результат:

Рис. 297. Результат запроса

Во многих ситуациях желательно, чтобы одинаковые строки в отчете не повторялись.

Пример:

// Необходимо узнать, каким вообще контрагентам
// отгружался товар за период.
Выбрать
Документ.РасходнаяНакладная.Контрагент

Результат:

Рис. 298. Результат запроса

Видно, что в результате запроса много повторяющихся строк, что снижает его наглядность. Чтобы избежать повторений, в описании запроса следует указать ключевое слово РАЗЛИЧНЫЕ.

Пример:

Выбрать Различные
Документ.РасходнаяНакладная.Контрагент

Результат:

Рис. 299. Результат запроса

В некоторых случаях необходимо вывести в отчет ограниченное количество строк. Для этого в описании запроса следует указать ключевое слово ПЕРВЫЕ и после него – требуемое количество строк.

Пример:

// Необходимо отобрать пять самых дорогих товаров.
// Выборка должна осуществляться в порядке убывания цены товара.
Выбрать Первые 5
Справочник.Номенклатура.Наименование,
Справочник.Номенклатура.ЗакупочнаяЦена
Упорядочить По
Справочник.Номенклатура.ЗакупочнаяЦена Убыв

Результат:

Рис. 300. Результат запроса

После обязательного ключевого слова ВЫБРАТЬ (и уточняющих слов РАЗЛИЧНЫЕ и ПЕРВЫЕ) в тексте запроса задается список полей выборки. Эти поля будут обрабатываться при выборке данных в запросе. Результат запроса также будет иметь набор полей, определенный в данном списке. Поля выборки описываются по следующим правилам:

<Список полей выборки>

<Поле выборки>[, <Поле выборки>[, …]] | *

<Поле выборки>

<Описание поля> | <Логическое выражение> [[КАК] <Псевдоним поля>]

<Описание поля>

<Выражение>[.<Группа полей>]|<Описание пустой таблицы>

<Описание пустой таблицы>

ПУСТАЯТАБЛИЦА.(<Список псевдонимов>)

<Список псевдонимов>

[<Псевдоним поля>][,<Список псевдонимов>]

Список полей выборки состоит из одного или нескольких элементов, которые разделены запятыми. Каждое поле выборки состоит из описания поля выборки и необязательного псевдонима поля.

Вместо перечисления полей в списке выборки можно указать звездочку (*). Это будет означать, что результат запроса должен содержать все поля, которые есть в исходных таблицах – источниках данных запроса, описанных в списке источников.

Примечание 1. При указании звездочки (*) в списке полей выборки в результат не включаются виртуальные поля исходных таблиц.

Примечание 2. Получение выборок очень большого размера (более 64 Мб) требует наличия достаточного количества свободного места на диске, используемом для размещения временных файлов сервера и клиента.

Описание поля определяет, каким образом должны формироваться значения поля. В простейшем случае поле выборки является ссылкой на поле исходной таблицы. Ссылка может задаваться с указанием таблицы, содержащей это поле, или без указания самой таблицы. Разыменование полей см. здесь.

В общем случае поле выборки может представлять собой не только ссылку на поле исходной таблицы, а некоторое выражение (подробнее см. здесь) или логическое выражение (см. здесь).

Результаты запроса могут быть сгруппированы (см. здесь) с помощью агрегатных функций (см. здесь), указанных в качестве выражений в полях выборки.

Каждому полю выборки может быть назначен псевдоним (см. здесь). В дальнейшем псевдоним поля может использоваться для более удобного обращения к данному полю.

Группа полей может указываться только тогда, когда поле выборки указывает на вложенную таблицу (см. здесь). В этом случае можно указать, какие поля должны обрабатываться в выборке по вложенной таблице. Если группа полей не указана, в выборке будут обрабатываться все поля вложенной таблицы.

Если полю выборки назначить псевдоним, то в дальнейшем к этому полю можно будет обращаться, используя его псевдоним, в предложениях УПОРЯДОЧИТЬ ПО и ИТОГИ, а также при работе с результатом запроса. Такое обращение может быть более удобным и наглядным, а в некоторых случаях единственно возможным.

Ключевое слово КАК может предшествовать псевдониму поля. Это слово можно не указывать вообще, но если оно указано, повышается наглядность и удобочитаемость текста запроса.

Псевдонимы полей задаются в соответствии с правилами назначения идентификаторов переменных. Псевдонимы в запросе не могут совпадать.

Назначение псевдонимов полям само по себе никак не влияет на выборку данных в запросе.

Пример:

// Необходимо выбрать из справочника товаров
// наименования товаров и наименования групп.
Выбрать
Справочник.Номенклатура.Наименование Как Товар,
Справочник.Номенклатура.Родитель.Наименование Как Группа
Из
Справочник.Номенклатура

Результат:

Рис. 301. Результат запроса (фрагмент)

Следует обратить внимание, что поля в результате запроса называются Товар и Группа. Если бы псевдонимы полей не были указаны, поля в результате запроса назывались бы Наименование и Наименование1 (названия полей в результате запроса не могут совпадать, поэтому к названию второго поля автоматически добавлено «1»), что менее наглядно.

Поле в списке выборки может ссылаться на вложенную таблицу источника данных запроса. В этом случае поле результата запроса будет иметь тип РезультатЗапроса, то есть содержать вложенный результат запроса, сформированный на основе вложенной таблицы – источника.

По умолчанию во вложенный результат включаются все поля вложенной таблицы – источника данных. Имеется возможность явно определить группу полей, которые должны содержаться во вложенном результате запроса. Группа полей вложенного результата описывается по следующему правилу:

<Группа полей>

( <Список вложенных полей> ) | *

<Список вложенных полей>

<Вложенное поле>[, <Вложенное поле>[, …]]

<Вложенное поле>

<Выражение> [[КАК] <Псевдоним поля>]

Список вложенных полей состоит из одного или нескольких элементов, разделенных запятыми. Если список состоит из одного элемента, его не обязательно заключать в скобки.

Вместо перечисления вложенных полей можно указать звездочку (*); это будет означать, что результат запроса должен содержать все поля, которые есть во вложенной таблице.

Вложенное поле может представлять некоторое выражение (см. здесь). В простейшем случае выражение – это ссылка на поле вложенной таблицы.

Каждому вложенному полю может быть назначен псевдоним. В дальнейшем псевдоним поля может использоваться для более удобного обращения к данному полю, аналогично псевдонимам полей списка выборки – см. здесь.

Псевдонимы вложенным полям могут быть назначены независимо от того, задан ли псевдоним самой вложенной таблице.

Пример:

// В отчет необходимо вывести спецификацию товарных накладных -
// сам документ, номенклатуру и количество.
Выбрать
Документ.РасходнаяНакладная.Ссылка,
Документ.РасходнаяНакладная.Состав.(Номенклатура Как Товар,
                    Количество)

Результат:

Рис. 302. Результат запроса (фрагмент)

Следует обратить внимание, что поле Состав результата запроса представляет собой вложенную таблицу, имеющую поля Номенклатура и Количество.

Если в запросе используется объединение и в некоторых частях объединения присутствуют вложенные таблицы, а в некоторых – нет, возникает необходимость дополнения списка выборки полями – пустыми вложенными таблицами. Делается это при помощи ключевого слова ПУСТАЯТАБЛИЦА. После него в скобках указываются псевдонимы полей, из которых будет состоять вложенная таблица.

Пример:

ВЫБРАТЬ Ссылка.Номер, ПУСТАЯТАБЛИЦА.(Ном, Тов, Кол) КАК Состав
ИЗ Документ.РасходнаяНакладная
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Ссылка.Номер, Состав.(НомерСтроки, Номенклатура, Количество)
ИЗ Документ.РасходнаяНакладная

Задача предложения ИЗ состоит в том, чтобы обозначить список исходных таблиц – источников данных, используемых в данном операторе ВЫБРАТЬ.

Следует отметить, что предложение ИЗ в языке запросов является опциональным. Оно может быть опущено в том случае, если источники данных полностью квалифицированы в описании списка полей выборки, содержащегося в предложении ВЫБРАТЬ. Следует обратить внимание, что ряд примеров в предыдущих разделах не содержал предложения ИЗ.

После ключевого слова ИЗ указывается список источников. В общем случае список источников описывается следующим набором правил:

<Список источников>

<Источник>[, <Источник>[, …]]

<Источник>

<Описание источника> [ <Перечень соединений> ]

<Описание источника>

<Таблица> [[КАК] <Псевдоним источника>]

<Таблица>

<Имя таблицы>[(<Параметры>)] | (<Описание запроса>)

Источники данных запроса перечисляются в списке источников через запятую. Каждый источник в списке источников обязательно включает в себя описание источника; кроме того, может быть указан перечень соединений – правила соединений источника с другими источниками. Описание спецификации соединений см. здесь.

Если в качестве источника данных выступает таблица информационной базы, описание источника содержит имя таблицы.

Если исходная таблица виртуальная, могут быть указаны параметры ее формирования. Подробно параметры виртуальных таблиц описаны в разделе Встроенный язык – Работа с запросами – Таблицы запросов встроенной справки.

В качестве источника данных запроса может выступать также вложенный запрос. В этом случае описание источника содержит описание запроса. Описание использования вложенных запросов см. здесь.

В описании источника данных может быть также назначен его псевдоним (см. здесь). В дальнейшем псевдоним источника может использоваться для более удобного обращения к данному источнику.

При определении нескольких источников в списке для каждой записи из первой таблицы-источника осуществляется выборка из второй таблицы-источника и т. д. Таким образом, в результате запроса формируются все возможные комбинации всех записей из всех указанных источников.

Пример:

ВЫБРАТЬ
Контрагенты.Ссылка как Контрагент,
ТипыЦен.Ссылка КАК ТипЦены
ИЗ
Справочник.Контрагенты КАК Контрагенты,
Справочник.ТипыЦен КАК ТипыЦенРезультат

Результат:

Рис. 303. Результат запроса (фрагмент)

Результат запроса содержит комбинации всех контрагентов со всеми типами цен. Как правило, такой результат сам по себе смысла не имеет. Обычно комбинации записей из разных исходных таблиц требуется ограничить какими-либо условиями. В языке запросов имеется возможность описать такое соединение источников, указывая сами источники и определяя условия, в соответствии с которыми комбинации записей из этих источников требуется включить в результат запроса.

Соединения бывают нескольких видов; они описываются следующими правилами:

<Перечень соединений>

<Соединение> [<Перечень соединений>]

<Соединение>

[ВНУТРЕННЕЕ] СОЕДИНЕНИЕ <Описание источника> ПО <Условие отбора> |
ЛЕВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ <Описание источника> ПО <Условие отбора> |
ПРАВОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ <Описание источника> ПО <Условие отбора> |
ПОЛНОЕ [ВНЕШНЕЕ] СОЕДИНЕНИЕ <Описание источника> ПО <Условие отбора>

В общем случае перечень соединений может содержать и описывать не только одно соединение (двух источников), но и несколько соединений нескольких источников сразу.

Описание источника содержит описание исходной таблицы (см. здесь).

Условие отбора содержит условия, в соответствии с которыми в выборке необходимо соединить данные из исходных таблиц – источников запроса. Правила описания условий в языке запросов см. здесь.

Ключевые слова ЛЕВОЕ, ПРАВОЕ и ПОЛНОЕ уточняют характер соединения. Слова ВНУТРЕНЕЕ или ВНЕШНЕЕ можно не указывать вообще, они повышают наглядность и удобочитаемость текста запроса.

Соединяемые источники не равнозначны между собой, и в некоторых случаях результат зависит от того, какая таблица указана первой, до ключевого слова СОЕДИНЕНИЕ (слева от него), а какая – второй (справа).

Для описания соединений будем использовать две простые таблицы. Одна таблица называется Компании, состоит из двух полей: Наименование и Телефон и содержит следующие данные:

Рис. 304. Таблица «Компании»

Другая таблица называется Контакты, состоит из трех полей: Наименование, Телефон и Компания (ссылка на элемент таблицы Компании). Таблица содержит следующие данные:

Рис. 305. Таблица «Контакты»

Примечание. В терминах «1С:Предприятия» обе таблицы являются справочниками.

Внутреннее соединение означает, что из обеих исходных таблиц – источников данных в результат запроса необходимо включить только те комбинации записей, которые соответствуют указанному условию. Остальные записи в результат не попадают.

Пример:

ВЫБРАТЬ
    Компании.Наименование КАК Компания,
    Контакты.Наименование КАК Контакт
ИЗ
    Справочник.Контакты КАК Контакты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Компании КАК Компании
        ПО Контакты.Компания = Компании.Ссылка

Результат:

Рис. 306. Результат запроса

Левое внешнее соединение означает, что в результат запроса нужно включить комбинации записей из обеих исходных таблиц, которые соответствуют указанному условию. Но, в отличие от внутреннего соединения, в результат запроса нужно включить также еще и записи из первого (указанного слева от слова СОЕДИНЕНИЕ) источника, для которых не найдено соответствующих условию записей из второго источника.

Таким образом, в результат запроса будут включены все записи из первого источника; они будут соединены с записями из второго источника при выполнении указанного условия. Строки результата запроса, для которых не найдено соответствующих условию записей из второго источника, будут содержать NULL в полях, формируемых на основании записей из этого источника.

Пример:

ВЫБРАТЬ
    Контакты.Наименование КАК Контакт,
    Компании.Наименование КАК Компания
ИЗ
    Справочник.Контакты КАК Контакты
        ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Компании КАК Компании
        ПО Контакты.Компания = Компании.Ссылка

Результат:

Рис. 307. Результат запроса

Правое внешнее соединение означает, что в результат запроса нужно включить комбинации записей из обеих исходных таблиц, которые соответствуют указанному условию. Кроме того, в результат запроса нужно также включить и записи из второго (указанного справа от слова СОЕДИНЕНИЕ) источника, для которых не найдено соответствующих условию записей из первого источника.

Таким образом, в результат запроса будут включены все записи из второго источника; они будут соединены с записями из первого источника при выполнении указанного условия. Строки результата запроса, для которых не найдено соответствующих условию записей из первого источника, будут содержать NULL в полях, формируемых на основании записей из этого источника.

Примечание. Фактически правое внешнее соединение можно выразить через левое внешнее соединение. Именно так делает конструктор запросов конфигуратора.

Пример:

ВЫБРАТЬ
    Контакты.Наименование КАК Контакт,
    Компании.Наименование КАК Компания
ИЗ
    Справочник.Контакты КАК Контакты
        ПРАВОЕ СОЕДИНЕНИЕ Справочник.Компании КАК Компании
        ПО Контакты.Телефон = Компании.Телефон

Результат:

Рис. 308. Результат запроса

Полное внешнее соединение означает, что в результат запроса нужно включить комбинации записей из обеих исходных таблиц, которые соответствуют указанному условию. Кроме того, в результат запроса нужно также включить и те записи из обоих источников, для которых не найдено соответствий.

Таким образом, в результат запроса будут включены все записи из обоих источников; они будут соединены друг с другом при выполнении указанного условия. Строки результата запроса, для которых не найдено соответствующих условию записей из какого-либо источника, будут содержать NULL в полях, формируемых на основании записей из этого источника.

Пример:

ВЫБРАТЬ
    Контакты.Наименование КАК Контакт,
    Компании.Наименование КАК Компания
ИЗ
    Справочник.Компании КАК Компании
        ПОЛНОЕ СОЕДИНЕНИЕ Справочник.Контакты КАК Контакты
        ПО (Контакты.Телефон = Компании.Телефон)

Результат:

Рис. 309. Результат запроса

Если источнику данных назначить псевдоним, то в дальнейшем к этому источнику можно будет обращаться, используя этот псевдоним (и уже нельзя будет обращаться через указание имени таблицы). Такое обращение может быть более удобным и наглядным, а в некоторых случаях единственно возможным.

Псевдоним задается в соответствии с правилами назначения идентификаторов переменных. Псевдонимы в запросе не могут совпадать.

Ключевое слово КАК может предшествовать псевдониму источника. Это слово можно не указывать вообще, но если оно указано, повышается наглядность и удобочитаемость текста запроса.

Назначение псевдонимов источникам само по себе никак не влияет на выборку данных в запросе.

Пример:

ВЫБРАТЬ
    Товар.Наименование,
    Товар.Родитель
ИЗ
    Справочник.Номенклатура КАК Товар

Данный пример демонстрирует использование в списке полей выборки псевдонима Товар, назначенного исходной таблице Справочник.Номенклатура.

В списке источников могут фигурировать и вложенные таблицы – табличные части справочников и документов.

Пример:

// В отчет необходимо вывести спецификацию товарных накладных -
// показать сам документ, номенклатуру и количество.
// В списке источников указана вложенная таблица "Состав" -
// табличная часть накладной.
// Выборка ограничена восемью записями, чтобы не перегружать пример.
ВЫБРАТЬ ПЕРВЫЕ 8
    РасходнаяНакладнаяСостав.Ссылка,
    РасходнаяНакладнаяСостав.Номенклатура,
    РасходнаяНакладнаяСостав.Количество
ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав

Результат:

Рис. 310. Результат запроса

Следует обратить внимание, что при указании вложенной таблицы в списке источников допускается обращение как к полям самой вложенной таблицы, так и к полям таблицы верхнего уровня (той, которая содержит вложенную таблицу) через поле Ссылка. В данном случае происходит обращение к полю Ссылка.НаименованиеПоля самого документа.

В списке источников запроса в качестве таблицы-источника может использоваться вложенный запрос. В этом случае описание источника содержит описание вложенного запроса. Описание вложенного запроса составляется точно так же, как и обычного (см. здесь).

Использование вложенного запроса в качестве источника данных ничем не отличается от использования таблицы информационной базы. В качестве полей такого источника доступны все поля, описанные в списке полей выборки вложенного запроса.

Пример:

ВЫБРАТЬ
    ВложенныйЗапрос.Ссылка,
    ВложенныйЗапрос.Номенклатура,
    ВложенныйЗапрос.Количество
ИЗ
    (ВЫБРАТЬ ПЕРВЫЕ 8
        РасходнаяНакладнаяСостав.Ссылка КАК Ссылка,
        РасходнаяНакладнаяСостав.Номенклатура КАК Номенклатура,
        РасходнаяНакладнаяСостав.Количество КАК Количество
    ИЗ
Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав) КАК ВложенныйЗапрос

Результат будет точно таким же, как и в предыдущем примере.

Предложение ГДЕ <Условие отбора> позволяет задать условие отбора данных из исходных таблиц – источников запроса; в запросе будут обрабатываться только те записи, для которых данное условие оказывается истинным.

Пример:

// Необходимо выяснить, какие контрагенты являются частными лицами.
ВЫБРАТЬ
    Контрагенты.Наименование
ИЗ
    Справочник.Контрагенты КАК Контрагенты
ГДЕ Контрагенты.Вид = ЗНАЧЕНИЕ(Перечисление.ВидыКонтрагентов.ЧастноеЛицо)

Результат:

Рис. 311. Результат запроса

Примечание. Совершенно не обязательно, чтобы поле, фигурирующее в предложении ГДЕ, входило в список выборки.

Условие отбора может определяться и как простое логическое выражение, и как более сложное, в котором простые логические выражения соединяются между собой логическими операторами И, ИЛИ, НЕ. Описание условий в языке запросов см. здесь.

При выборке данных из таблицы, имеющей табличные части, условия на записи табличной части действуют как на состав записей, выбираемых из таблицы, так и на состав записей табличных частей.

В общем виде: выборка записей, содержащих табличные части, выполняется в следующем порядке:

● К каждой таблице из списка источников левым внешним соединением присоединяются ее табличные части.

● Выполняются все соединения, определенные в списке источников.

● Отбираются записи, удовлетворяющие условию из раздела ГДЕ.

● Выполняется группировка записей по полям таблиц из списка источников, включая поля ссылок, с помещением записей табличных частей в качестве значений соответствующих полей записей таблиц из списка выборки.

Исходные данные в запросе могут быть сгруппированы с помощью агрегатных функций, указанных в качестве полей в списке выборки. Это означает, что строки в результате запроса будут содержать результаты вычисления указанных агрегатных функций, рассчитанные (сгруппированные) по записям исходных таблиц.

Сами агрегатные функции указываются в списке полей выборки. В предложении СГРУППИРОВАТЬ ПО <Поля группировки> необходимо указать список полей, по которым следует произвести группировку. В запросе будут группироваться записи исходных таблиц, содержащие одинаковые значения указанных полей.

Список полей группировки содержит ссылки на поля исходных таблиц – источников запроса, указанные через запятую:

<Поля группировки>

<Разыменование поля> [, <Разыменование поля> [, …]]

ВНИМАНИЕ! При группировке результатов запроса в списке полей выборки обязательно должны быть указаны агрегатные функции, а помимо них допускается указывать только поля, по которым осуществляется группировка. В запросах, содержащих группировку по какому-либо полю, не следует использовать это поле внутри агрегатных функций. Использование поля одновременно для группировки и внутри агрегатной функции – следует считать ошибкой. Такие запросы могут трактоваться по-разному, в зависимости от того, какая СУБД используется. При нарушении данного правила возможны ошибки, приводящие к завершению программы.

Исключение составляют ситуации, когда агрегатные функции применены к полям вложенной таблицы. В этом случае в списке полей выборки возможны обращения к полям таблицы верхнего уровня, без группировки результатов по этим полям.

При использовании агрегатных функций предложение СГРУППИРОВАТЬ ПО может быть и не указано совсем; при этом все результаты запроса будут сгруппированы в одну-единственную строку.

Пример:

// Требуется получить статистику по продаже товаров:
// максимальную, минимальную и среднюю цены в расходных накладных.
Выбрать
    Накладная.Номенклатура,
    Среднее (Накладная.Цена) Как Среднее,
    Максимум(Накладная.Цена) Как Максимум,
    Минимум (Накладная.Цена) Как Минимум
Из
    Документ.РасходнаяНакладная.Состав Как Накладная
Сгруппировать По
    Накладная.Номенклатура

Результат:

Рис. 312. Результат запроса (фрагмент)

Предложение ИМЕЮЩИЕ <Условие отбора> позволяет накладывать условия на значения агрегатных функций. В других конструкциях языка запросов, например, в предложении ГДЕ, указывать в условиях агрегатные функции нельзя.

Пример:

// Необходимо выбрать товары, которых продали более 20 штук.
ВЫБРАТЬ
    Накладная.Номенклатура,
    СУММА(Накладная.Количество) КАК Количество
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Накладная
СГРУППИРОВАТЬ ПО
    Накладная.Номенклатура
ИМЕЮЩИЕ
    СУММА(Накладная.Количество) > 20

Результат:

Рис. 313. Результат запроса

ВНИМАНИЕ! В условии отбора можно использовать только агрегатные функции и поля, по которым осуществляется группировка.

В языке запросов имеется возможность объединять несколько запросов. При этом записи, полученные с помощью каждого из объединяемых запросов, будут собраны в один результат запроса.

При объединении каждый запрос собирает данные независимо, а такие операции, как упорядочивание результатов и расчет итогов, выполняются уже над результатом объединения запросов.

Поля результата запроса будут называться так, как описано в списке полей выборки первого из объединяемых запросов. Поля выборки остальных запросов сопоставляются с полями результата в соответствии с порядком их следования в списке полей выборки. Объединяемые запросы должны иметь одинаковое количество полей в списке полей выборки.

Если поля выборки объединяемых запросов имеют разный тип, то поля результата запроса будут иметь составной тип.

Объединение запросов описывается по следующему правилу:

<Объединение запросов>

ОБЪЕДИНИТЬ [ВСЕ]
<Описание запроса>
[<Объединение запросов>]

Объединение запросов начинается с обязательного ключевого слова ОБЪЕДИНИТЬ, после которого следует описание присоединяемого запроса. Далее может присоединяться еще один запрос и т. д.

По умолчанию при объединении запросов полностью одинаковые строки в результате запроса, сформированные разными запросами, заменяются одной. Если требуется, чтобы были оставлены разные строки, необходимо указать ключевое слово ВСЕ.

Пример:

ВЫБРАТЬ
    Приход.Номенклатура КАК Товар,
    СУММА(Приход.Количество) КАК Приход,
    СУММА(0) КАК Расход
ИЗ
    Документ.ПриходнаяНакладная.Состав КАК Приход
СГРУППИРОВАТЬ ПО
    Приход.Номенклатура
ОБЪЕДИНИТЬ
ВЫБРАТЬ
    Расход.Номенклатура,
    СУММА(0),
    СУММА(Расход.Количество)
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Расход
СГРУППИРОВАТЬ ПО
    Расход.Номенклатура

Результат:

Рис. 314. Результат запроса (фрагмент)

Предложение УПОРЯДОЧИТЬ ПО позволяет сортировать строки в результате запроса.

<Упорядочивание результатов>

УПОРЯДОЧИТЬ ПО <Условия упорядочивания>

<Условия упорядочивания>

<Поле упорядочивания> [<Порядок>]
[, <Поле упорядочивания> [<Порядок>][, …]]

<Поле упорядочивания>

<Выражение> <Порядок> ВОЗР | УБЫВ | ИЕРАРХИЯ | ИЕРАРХИЯ УБЫВ

В предложении УПОРЯДОЧИТЬ ПО через запятую перечисляются условия, в соответствии с которыми необходимо упорядочить результат запроса. Выборки упорядочиваются сначала по первому условию, потом по второму и т. д.

Условие упорядочивания в общем случае может представлять собой некоторое выражение (см. здесь). Строки результата запроса будут упорядочены по значениям этого выражения, рассчитанным для каждой строки.

Упорядочивание может осуществляться в порядке возрастания или убывания значений, а для таблиц, для которых задано свойство иерархичности, – также и по иерархии (см. здесь). Порядок может задаваться для каждого поля независимо. Описание правил сравнения значений см. здесь.

Поле, фигурирующее в условиях упорядочивания, совсем не обязательно должно попадать в результат запроса.

Пример:

// Требуется отобрать 5 самых дорогих товаров,
// расположив их в порядке убывания цены.
ВЫБРАТЬ ПЕРВЫЕ 5
    Номенклатура.Наименование,
    Номенклатура.ЗакупочнаяЦена
ИЗ
    Справочник.Номенклатура КАК Номенклатура
УПОРЯДОЧИТЬ ПО
    Номенклатура.ЗакупочнаяЦена УБЫВ

Результат:

Рис. 315. Результат запроса

Для справочников можно назначать упорядочивание по иерархии справочника.

Пример:

ВЫБРАТЬ
    Справочник.Номенклатура.Наименование,
    Справочник.Номенклатура.ПолноеНаименование
УПОРЯДОЧИТЬ ПО
    Справочник.Номенклатура.Наименование Иерархия

Результат:

Рис. 316. Результат запроса (фрагмент)

Иерархически сортировать можно только по полю, но не по некоторой операции над ним: поле упорядочивания должно содержать ссылку на поле исходной таблицы – источника данных запроса.

ВНИМАНИЕ! Упорядочивание по иерархии имеет смысл задавать в том случае, если в качестве источника определена именно таблица справочника, а не какая-либо другая таблица, содержащая только ссылку на справочник.

Пример:

ВЫБРАТЬ
    РасходнаяНакладная.Контрагент.Наименование,
    РасходнаяНакладная.Номер,
    РасходнаяНакладная.Склад,
ИЗ
    Документ.РасходнаяНакладная КАК РасходнаяНакладная

В данном примере иерархического упорядочивания не получится, поскольку нет связи со справочником, и группы из этого справочника в результат запроса не попадут.

Для упорядочивания по иерархии необходимо организовать соединение со справочником.

Пример:

ВЫБРАТЬ
    Контрагенты.Наименование КАК Наименование,
    РасходнаяНакладная.Номер,
    РасходнаяНакладная.Склад
ИЗ
    Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ Документ.РасходнаяНакладная КАК РасходнаяНакладная
ПО Контрагенты.Ссылка = РасходнаяНакладная.Контрагент
УПОРЯДОЧИТЬ ПО
    Наименование ИЕРАРХИЯ

Результат:

Рис. 317. Результат запроса (фрагмент)

В предложении УПОРЯДОЧИТЬ ПО можно определять также и условия упорядочивания записей из вложенных таблиц; причем их можно комбинировать с условиями упорядочивания по таблице верхнего уровня.

При этом важен порядок указания полей таблицы одного уровня (вложенной или верхнего уровня) относительно друг друга, но не важно, в каком порядке указаны поля таблицы одного уровня относительно полей таблицы другого уровня: упорядочивание выполняется всегда сначала по таблице верхнего уровня, а потом по вложенной таблице.

Пример:

// В отчет необходимо вывести спецификацию товарных накладных -
// показать сам документ, номенклатуру и количество.
// Документы требуется упорядочить по номеру,
// а состав – по наименованию товара.
Выбрать
    Документ.РасходнаяНакладная.Ссылка,
    Документ.РасходнаяНакладная.Состав.(Номенклатура Как Товар, Количество)
Упорядочить По
    Документ.РасходнаяНакладная.Номер,
    Документ.РасходнаяНакладная.Состав.Номенклатура.Наименование

Предложение АВТОУПОРЯДОЧИВАНИЕ позволяет включить режим автоматического формирования полей для упорядочивания результата запроса.

Автоупорядочивание работает по следующим принципам:

● Если в запросе было указано предложение УПОРЯДОЧИТЬ ПО, то каждая ссылка на таблицу, находящаяся в этом предложении, будет заменена полями, по которым по умолчанию сортируется таблица (для справочников это код или наименование, для документов – дата документа). Если поле для упорядочивания ссылается на иерархический справочник, то будет применена иерархическая сортировка по этому справочнику.

● Если в запросе отсутствует предложение УПОРЯДОЧИТЬ ПО, но есть предложение ИТОГИ, тогда результат запроса будет упорядочен по полям, присутствующим в предложении ИТОГИ после ключевого слова ПО, в той же последовательности; и если итоги рассчитывались по полям-ссылкам, то по полям сортировки по умолчанию таблиц, на которые были ссылки.

● Если в запросе отсутствуют предложения УПОРЯДОЧИТЬ ПО и ИТОГИ, но есть предложение СГРУППИРОВАТЬ ПО, тогда результат запроса будет упорядочен по полям, присутствующим в предложении, в той же последовательности; и если группировка велась по полям-ссылкам, то по полям сортировки по умолчанию таблиц, на которые были ссылки.

● Если в запросе отсутствуют предложения УПОРЯДОЧИТЬ ПО, ИТОГИ и СГРУППИРОВАТЬ ПО, результат будет упорядочен по полям сортировки по умолчанию для таблиц, из которых выбираются данные, в порядке их появления в запросе.

Предложение ИТОГИ позволяет определить, расчет каких итогов необходим в запросе. При расчете итогов вычисляются значения агрегатных функций по выборкам с одинаковыми значениями полей – контрольных точек. Итоги добавляются в результат запроса как итоговые строки.

Порядок расчета итогов запроса описывается в соответствии со следующими правилами:

<Описание итогов>

<Итоги> [<Описание итогов>]

<Итоги>

ИТОГИ [<Список итоговых полей>] ПО [ОБЩИЕ] <Список контрольных точек>

<Список итоговых полей>

<Итоговое поле> [,<Список итоговых полей> [, …]]

<Итоговое поле>

<Агрегатная функция> | <Выражение> [[КАК] <Псевдоним поля>]

<Список контрольных точек>

<Контрольная точка> [, <Контрольная точка> [, …]]

<Контрольная точка>

<Выражение> [[ТОЛЬКО] ИЕРАРХИЯ] | [ПЕРИОДАМИ(Секунда | Минута | Час | День | Неделя | Месяц | Квартал | Год | Декада | Полугодие
[,<Литерал типа DATE> | <Идентификатор параметра>]
[,<Литерал типа DATE> | <Идентификатор параметра>])] [[КАК] Псевдоним поля]

Описание итогов начинается с обязательного ключевого слова ИТОГИ.

Список агрегатных функций содержит перечень агрегатных функций (см. здесь), которые необходимо рассчитывать в итогах.

Ключевое слово ОБЩИЕ означает, что необходимо сформировать итоговую строку по всему результату запроса. Описание расчета общих итогов см. здесь.

Помимо общих итогов можно задать расчет итогов по контрольным точкам. Для этого после обязательного ключевого слова ПО необходимо указать <Список контрольных точек>. Каждая контрольная точка содержит выражение, вычисляемое при выполнении запроса. По каждой комбинации значений этих выражений будут рассчитаны и добавлены в результат запроса итоговые строки.

Если контрольная точка является ссылкой на справочник, возможен расчет итогов по иерархии справочника (см. здесь). Для этого после такой ссылки нужно указать обязательное ключевое слово ИЕРАРХИЯ.

В настоящей версии программы не поддерживается расчет итогов по вложенным таблицам.

Есть возможность рассчитать итоги по иерархии. Для этого после имени поля, для которого вычисляются итоги, необходимо указать ключевое слово ИЕРАРХИЯ. В результате будут рассчитаны итоги по контрольным точкам и итоги по иерархии для контрольных точек.

Пример:

ВЫБРАТЬ
    Документ.Номенклатура КАК Номенклатура,
    Документ.Количество КАК Количество,
    Документ.Ссылка.Номер,
    Документ.Ссылка.Контрагент
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Документ
УПОРЯДОЧИТЬ ПО
    Документ.Номенклатура
ИТОГИ
    СУММА(Количество)
ПО
    Номенклатура ИЕРАРХИЯ

Результат:

Рис. 318. Результат запроса (фрагмент)

При необходимости можно рассчитать только итоги значений по иерархии, без расчета итогов в контрольных точках. Для этого перед ключевым словом ИЕРАРХИЯ нужно указать ключевое слово ТОЛЬКО.

Пример:

ВЫБРАТЬ
    Документ.Номенклатура КАК Номенклатура,
    Документ.Количество КАК Количество,
    Документ.Ссылка.Номер,
    Документ.Ссылка.Контрагент
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Документ
УПОРЯДОЧИТЬ ПО
    Документ.Номенклатура
ИТОГИ
    СУММА(Количество)
ПО
    Номенклатура ТОЛЬКО ИЕРАРХИЯ

Результат:

Рис. 319. Результат запроса (фрагмент)

В случае если поле, по которому рассчитываем итоги, является полем типа Дата, возможно дополнение результатов датами в заданном периоде. Делается это при помощи ключевого слова ПЕРИОДАМИ, после которого в скобках указывается вид периода (Секунда, Минута, Час, День, Неделя, Месяц, Квартал, Год, Декада, Полугодие), начальная и конечная даты интересуемого периода. Если начальная и конечная даты не указаны, будут использованы первая и последняя даты, участвующие в результате.

Пример:

// Получить количество покупок по клиентам по часам выбранного дня
ВЫБРАТЬ
    ПриходнаяНакладная.Контрагент,
    НАЧАЛОПЕРИОДА(ПриходнаяНакладная.Дата, ЧАС) КАК Период,
    КОЛИЧЕСТВО(ПриходнаяНакладная.Ссылка) КАК КоличествоПокупок
ИЗ
    Документ.ПриходнаяНакладная КАК ПриходнаяНакладная
СГРУППИРОВАТЬ ПО
    ПриходнаяНакладная.Контрагент,
    НАЧАЛОПЕРИОДА(ПриходнаяНакладная.Дата, ЧАС)
ИТОГИ
    СУММА(КоличествоПокупок)
ПО
Период ПЕРИОДАМИ(МИНУТА, ДАТАВРЕМЯ(2006,6,28), ДАТАВРЕМЯ(2006,6,28))

Результат:

Рис. 320. Результат запроса

Такое представление результата получится, только если при обходе результата по группировке Период использовать в качестве источника измерения все записи периода.

Для расчета итогов по всей таблице в предложении ИТОГИ следует указать слово ОБЩИЕ. В этом случае будут вычислены значения агрегатных функций для всех записей таблицы.

Пример:

ВЫБРАТЬ
    Документ.Номенклатура,
    Документ.Количество КАК Количество,
    Документ.Ссылка.Номер,
    Документ.Ссылка.Контрагент
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Документ
ИТОГИ
    СУММА(Количество)
ПО
    ОБЩИЕ

Результат:

Рис. 321. Результат запроса (фрагмент)

Если итоги используются совместно с группировкой и для итогов не указан список агрегатных функций, он будет автоматически формироваться из агрегатных полей списка выборки. Если запрос содержит объединение, агрегатные функции будут браться из первого запроса.

Пример:

ВЫБРАТЬ
    Документ.Номенклатура КАК Номенклатура,
    Документ.Ссылка.Контрагент КАК Контрагент,
    СУММА(Документ.Количество) КАК Количество
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Документ
СГРУППИРОВАТЬ ПО
    Документ.Номенклатура,
    Документ.Ссылка.Контрагент
ИТОГИ ПО
    Номенклатура,
    Контрагент

Результат:

Рис. 322. Результат запроса (фрагмент)

Полям итогов – контрольным точкам, для которых считаются итоги, можно назначить псевдоним, для последующего обращения к ним из встроенного языка предприятия. Для этого после выражения – контрольной точки необходимо указать имя псевдонима аналогично тому, как это делается в списке полей выборки.

Пример:

ВЫБРАТЬ
    Документ.Номенклатура КАК Номенклатура,
    Документ.Количество КАК Количество,
    Документ.Ссылка.Номер,
    Документ.Ссылка.Контрагент
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Документ
УПОРЯДОЧИТЬ ПО
    Документ.Номенклатура
ИТОГИ
    СУММА(Количество)
ПО
    Номенклатура ТОЛЬКО ИЕРАРХИЯ КАК Товары

В случае, если псевдоним не указан, система сама даст имя итогу так, чтобы оно было уникально. В приведенном выше примере итог будет иметь имя Товары.

Во многих конструкциях языка запросов могут использоваться выражения. Выражения языка запросов описываются следующим набором правил:

<Выражение>

<Разыменование поля> |
<Агрегатная функция> |
<Встроенная функция> |
<Операция выбора> |
<Приведение типа>[.<Разыменование поля>] |
<Значение> |
<Выражение> <Бинарная операция> <Выражение> |
<Унарная операция> <Выражение> |
( <Выражение> )

<Бинарная операция>

+ | – | * | /

<Унарная операция>

– | +

В простейшем случае выражение является ссылкой на поле исходной таблицы – источника данных запроса. Ссылка может задаваться с указанием таблицы, содержащей это поле, или без указания самой таблицы. Описание разыменования полей см. здесь.

Выражения в списке полей выборки, в предложениях ИМЕЮЩИЕ, ИТОГИ, УПОРЯДОЧИТЬ ПО, могут быть агрегатными функциями (см. здесь).

Выражение может быть встроенной функцией языка запросов (см. здесь). Могут использоваться операции выбора (см. здесь) и операции приведения типа значения (см. здесь).

В выражениях могут непосредственно указываться значения логических, числовых, строковых и др. констант; также могут использоваться значения параметров запроса (см. здесь). В выражениях к значениям соответствующих типов могут применяться бинарные и унарные операции. При использовании операции деления рекомендуется делать проверку делителя на отличие от нуля.

Выражения языка запросов в простейшем случае представляют собой ссылки на поля таблиц информационной базы. В общем виде ссылки описываются следующими правилами:

<Разыменование поля>

[<Таблица>.]<Имя поля>[.<Имя поля>[…]]

<Таблица>

<Имя таблицы> | <Псевдоним источника>

Разыменование поля начинается с имени таблицы, содержащей это поле. Если имя поля уникально – существует только у одной из таблиц среди указанных в списке источников, таблица может быть опущена.

Если поле имеет ссылочный тип, язык запросов позволяет обращаться к полям таблицы, на которую ссылается поле, и так далее. Имена полей указываются через точку.

Если исходной таблице в списке источников присвоен псевдоним источника, он может использоваться вместо имени таблицы в разыменовании полей этой таблицы. В противном случае указывается имя таблицы (см. здесь).

В языке запросов предусмотрены агрегатные функции, которые используются при группировке результатов запроса и при подсчете итогов. Агрегатные функции предназначены для обобщения значений указанного параметра. Определены следующие агрегатные функции:

<Агрегатная функция>

СУММА ( <Выражение> ) |
СРЕДНЕЕ ( <Выражение> ) |
МИНИМУМ ( <Выражение> ) |
МАКСИМУМ ( <Выражение> ) |
КОЛИЧЕСТВО ( [РАЗЛИЧНЫЕ] <Выражение> | * )

Пример:

ВЫБРАТЬ
    Накладная.Номенклатура.Наименование,
    СУММА(Накладная.Сумма) КАК Сумма,
    СРЕДНЕЕ(Накладная.Сумма) КАК Среднее,
    МАКСИМУМ(Накладная.Сумма) КАК Максимум,
    МИНИМУМ(Накладная.Сумма) КАК Минимум,
    КОЛИЧЕСТВО(Накладная.Сумма) КАК Колич
ИЗ
 Документ.РасходнаяНакладная.Состав КАК Накладная
СГРУППИРОВАТЬ ПО
    Накладная.Номенклатура,
    Накладная.Номенклатура.Наименование
ИТОГИ ПО
    ОБЩИЕ

Результат:

Рис. 323. Результат запроса (фрагмент)

Агрегатные функции могут использоваться в списке полей выборки, в предложениях ИМЕЮЩИЕ, ИТОГИ, УПОРЯДОЧИТЬ ПО. В списке полей выборки агрегатные функции следует использовать только для получения результата. Не рекомендуется использовать агрегатные функции в списке полей выборки для сравнения.

Корректный запрос:

ВЫБРАТЬ
        КОЛИЧЕСТВО(*),
   Поставщик
ИЗ
    Документ.РасходнаяНакладная КАК Накладная
СГРУППИРОВАТЬ ПО
    Поставщик
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(*) > 1

Некорректный запрос:

ВЫБРАТЬ
        КОГДА КОЛИЧЕСТВО(*) > 1 ТОГДА Истина ИНАЧЕ Ложь КОНЕЦ,
        Поставщик
ИЗ
    Документ.РасходнаяНакладная КАК Накладная
СГРУППИРОВАТЬ ПО
    Поставщик

Несмотря на то, что некорректный запрос может работать успешно, такие конструкции (участие агрегатной функции не для получения результата,
а для проверки условия) в разделе ВЫБРАТЬ имеют свою специфику на разных СУБД и могут приводить к ошибкам.

СУММА

Описание:

Функция вычисляет арифметическую сумму всех попавших в выборку значений поля.

В качестве параметра функции можно указывать только поля, содержащие числовое значение.

Если поле не может содержать числовых значений, то применение функции СУММА к такому полю вызовет ошибку. Данная функция может быть применена к такому полю, только если поле может содержать числовые значения (имеет составной тип данных). Но если среди значений поля в выборке встретится нечисловое значение (помимо значений NULL), это вызовет ошибку.

СРЕДНЕЕ

Описание:

Функция вычисляет среднее значение всех попавших в выборку значений поля.

В качестве параметра функции можно указывать только ссылки на поля, содержащие числовое значение.

Если поле не может содержать числовых значений, то применение функции СРЕДНЕЕ к такому полю вызовет ошибку. Данная функция может быть применена к такому полю в том случае, если поле может содержать числовые значения (имеет составной тип данных). Но если среди значений поля в выборке встретится нечисловое значение (помимо значений NULL), это вызовет ошибку.

МИНИМУМ

Описание:

Функция вычисляет минимальное значение из всех попавших в выборку значений поля.

В качестве параметра функции можно указывать ссылки на поля, содержащие значения любого типа.

При определении минимального значения применяются правила сравнения значений, описанные в разделе «Правила сравнения значений».

МАКСИМУМ

Описание:

Функция вычисляет максимальное значение из всех попавших в выборку значений поля.

В качестве параметра функции можно указывать выражения, содержащие значения любого типа. Не может применяться к выражениям с типом ХранилищеЗначения, МоментВремени и строкам неограниченной длины.

При определении максимального значения применяются правила сравнения значений, описанные в разделе «Правила сравнения значений».

КОЛИЧЕСТВО

Описание:

Функция подсчитывает количество значений параметра, попавших в выборку. В отличие от других агрегатных функций, функция КОЛИЧЕСТВО допускает три способа использования:

● Функция позволяет подсчитать количество значений указанного поля, не равных NULL.

● Функция позволяет подсчитать количество различных значений указанного поля, не равных NULL. Для этого перед спецификацией поля нужно указать ключевое слово РАЗЛИЧНЫЕ.

● Функция позволяет подсчитать количество строк в результате запроса. Для этого в качестве параметра функции нужно указать звездочку «*».

В качестве параметра функции можно указывать ссылки на поля, содержащие значения любого типа.

Пример:

ВЫБРАТЬ
    КОЛИЧЕСТВО(*) КАК Всего,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Накладная.Номенклатура) КАК Разные
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Накладная

Результат:

Рис. 324. Результат запроса

В языке запросов определены встроенные функции, которые могут использоваться в выражениях в списке полей выборки (см. здесь) и в условии отбора в предложении ГДЕ (см. здесь).

Определены следующие встроенные функции:

<Встроенная функция>

ПОДСТРОКА ( <Выражение>, <Значение>, <Значение> ) |
ГОД ( <Выражение> ) |
КВАРТАЛ ( <Выражение> ) |
МЕСЯЦ ( <Выражение> ) |
ДЕНЬГОДА ( <Выражение> ) |
ДЕНЬ ( <Выражение> ) |
НЕДЕЛЯ ( <Выражение> ) |
ДЕНЬНЕДЕЛИ ( <Выражение> ) |
ЧАС ( <Выражение> ) |
МИНУТА ( <Выражение> ) |
СЕКУНДА ( <Выражение> ) |
НАЧАЛОПЕРИОДА(<Выражение>, Минута | Час | День | Неделя | Месяц | Квартал | Год | Декада | Полугодие) |
КОНЕЦПЕРИОДА(<Выражение>, Минута | Час | День | Неделя | Месяц | Квартал | Год | Декада | Полугодие) |
ДОБАВИТЬКДАТЕ (<Выражение>, Минута | Час | День | Неделя | Месяц | Квартал | Год | Декада | Полугодие, <Выражение>) |
РАЗНОСТЬДАТ(<Выражение>, <Выражение>, Секунда | Минута | Час | День | Месяц | Квартал | Год) |
ТИПЗНАЧЕНИЯ(<Выражение>) |
ПРЕДСТАВЛЕНИЕ(<Выражение>) |
ЕСТЬNULL(<Выражение>, <Выражение>)

ПОДСТРОКА

Описание:

Данная функция предназначена для выделения подстроки из строки. В функцию передаются три параметра:

● Строка, из которой необходимо выделить подстроку. Выражение, имеющее тип Строка.

● Позиция символа, с которого начинается выделяемая из строки подстрока. Значение типа Число.

● Длина выделяемой подстроки. Значение типа Число.

Если в качестве первого параметра фигурирует строка, то результатом функции будет строка (возможно, нулевой длины). Если в качестве первого параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

Пример:

ВЫБРАТЬ ПЕРВЫЕ 8
    Контрагенты.Наименование,
    ПОДСТРОКА(Контрагенты.Наименование, 3, 5) КАК Подстрока
ИЗ
    Справочник.Контрагенты КАК Контрагенты

Результат:

Рис. 325. Результат запроса

ГОД

Описание:

Данная функция предназначена для вычисления номера года из значения типа Дата.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

КВАРТАЛ

Описание:

Данная функция предназначена для вычисления номера квартала из значения типа Дата. Номер квартала находится в диапазоне 1–4.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

МЕСЯЦ

Описание:

Данная функция предназначена для вычисления номера месяца из значения типа Дата. Номер месяца находится в диапазоне 1–12.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

ДЕНЬГОДА

Описание:

Данная функция предназначена для вычисления дня года из значения типа Дата. День года находится в диапазоне 1–366.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

ДЕНЬ

Описание:

Данная функция предназначена для вычисления дня месяца из значения типа Дата. День месяца находится в диапазоне 1–31.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

НЕДЕЛЯ

Описание:

Данная функция предназначена для вычисления номера недели года из значения типа Дата.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки. При вычислении учитывается значение свойства Первый день недели региональных установок информационной базы (подробнее см. здесь).

ДЕНЬНЕДЕЛИ

Описание:

Данная функция предназначена для вычисления дня недели из значения типа Дата. День недели находится в диапазоне 1 (понедельник) – 7 (воскресенье).

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

ЧАС

Описание:

Данная функция предназначена для вычисления часа суток из значения типа Дата. Час суток находится в диапазоне 0–23.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

МИНУТА

Описание:

Данная функция предназначена для вычисления минуты часа из значения типа Дата. Минута часа находится в диапазоне 0–59.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

СЕКУНДА

Описание:

Данная функция предназначена для вычисления секунды минуты из значения типа Дата. Секунда минуты находится в диапазоне 0–59.

Параметр функции – это выражение, имеющее тип Дата.

Если в качестве параметра фигурирует значение типа Дата, то результатом функции будет значение типа Число. Если в качестве параметра будет использовано значение NULL, то результатом функции также будет значение NULL. Другие значения считаются недопустимыми и вызывают состояние ошибки.

НАЧАЛОПЕРИОДА

Описание:

Функция предназначена для выделения определенной даты из заданной даты.

Параметры функции – это выражение, имеющее тип Дата и тип периода – одно из значений: Минута, Час, День, Неделя, Месяц, Квартал, Год, Декада, Полугодие. Если тип периода указан Неделя, то учитывается значение свойства Первый день недели региональных установок информационной базы (подробнее см. здесь).

Пример:

ВЫБРАТЬ НАЧАЛОПЕРИОДА(ДАТАВРЕМЯ (2002, 10, 12, 10, 15, 34), МЕСЯЦ)

Результат:

Рис. 326. Результат запроса

Пример:

ВЫБРАТЬ НАЧАЛОПЕРИОДА(ДАТАВРЕМЯ(2002, 10, 12, 10, 15, 34), ДЕНЬ)

Результат:

Рис. 327. Результат запроса

КОНЕЦПЕРИОДА

Описание:

Функция предназначена для выделения определенной даты из заданной даты.

Параметры функции – это выражение, имеющее тип Дата и тип периода – одно из значений: Минута, Час, День, Неделя, Месяц, Квартал, Год, Декада, Полугодие. Если тип периода указан Неделя, то учитывается значение свойства Первый день недели региональных установок информационной базы (подробнее см. здесь).

Пример:

ВЫБРАТЬ КОНЕЦПЕРИОДА(ДАТАВРЕМЯ(2002, 10, 12, 10, 15, 34), МЕСЯЦ)

Результат:

Рис. 328. Результат запроса

Пример:

ВЫБРАТЬ КОНЕЦПЕРИОДА(ДАТАВРЕМЯ(2002, 10, 12, 10, 15, 34), ГОД)

Результат:

Рис. 329. Результат запроса

ДОБАВИТЬКДАТЕ

Описание:

Функция предназначена для прибавления к дате некоторой величины.

Первый параметр – исходная дата, к значению которой требуется добавить заданную величину, определяемую вторым и третьим параметрами; выражение, имеющее тип Дата.

Второй параметр – тип увеличения, одно из значений: Секунда, Минута, Час, День, Неделя, Месяц, Квартал, Год, Декада, Полугодие.

Третий параметр – величина, на которую требуется увеличить дату, задаваемую первым параметром; тип Число (дробная часть игнорируется).

Рекомендуется третий параметр (размерность увеличения) указывать так, чтобы он соответствовал решаемой прикладной задаче. Так, если прикладная задача оперирует днями или месяцами, то следует добавлять к дате именно дни и месяцы. Данная рекомендация обусловлена тем, что добавление к дате значения с очень высокой дискретностью (секунды, минуты, часы) может по-разному учитывать переходы на зимнее/летнее время на разных СУБД.

Пример:

ВЫБРАТЬ    ДОБАВИТЬКДАТЕ(ДАТАВРЕМЯ(2002, 10, 12, 10, 15, 34), МЕСЯЦ, 1)

Результат:

Рис. 330. Результат запроса

Пример:

ВЫБРАТЬ ДобавитьКДате(ДатаВремя(2002, 10, 12, 10, 15, 34), День, 5)

Результат:

Рис. 331. Результат запроса

РАЗНОСТЬДАТ

Описание:

Функция предназначена для получения разницы между двумя датами.

Первый параметр – выражение типа Дата. Второй параметр – выражение типа Дата. Третий параметр – тип разности, одно из значений: Секунда, Минута, Час, День, Месяц, Квартал, Год.

Рекомендуется третий параметр (тип разности) указывать так, чтобы он соответствовал решаемой прикладной задаче. Так, если прикладная задача оперирует днями или месяцами, то следует вычислять разность дат именно в днях или месяцах. Не рекомендуется получать неадекватную (с точки зрения прикладной задачи) разность дат, а потом средствами языка запросов или встроенного языка приводить ее к желаемой разнице. Данная рекомендация обусловлена тем, что расчет разницы с очень высокой дискретностью (секунды, минуты, часы) может по-разному учитывать переходы на зимнее/летнее время на разных СУБД.

Не поддерживается использование данной функции при задании значения параметра виртуальной таблицы Период.

Пример:

ВЫБРАТЬ РАЗНОСТЬДАТ(ДАТАВРЕМЯ(2002, 10, 12, 10, 15, 34), ДАТАВРЕМЯ(2002, 10, 14, 9, 18, 06), ДЕНЬ)

Результат:

Рис. 332. Результат запроса

Пример:

ВЫБРАТЬ РАЗНОСТЬДАТ(ДатаВремя(2002, 10, 12), ДатаВремя(2002, 11, 03), МЕСЯЦ)

Результат:

Рис. 333. Результат запроса

ВНИМАНИЕ! Функция рассчитывает календарную разницу между двумя датами, поэтому ее нельзя использовать там, где необходимо рассчитать количество банковских или рабочих дней между двумя датами.

Функция ТИПЗНАЧЕНИЯ

Описание:

Функция определения типа значения в запросе.

Параметры:

Параметр функции – выражение любого типа.

Возвращаемое значение – значение типа Тип.

Если параметром функции является значение Неопределено, то возвращаемое значение также будет Неопределено.

Пример:

ВЫБРАТЬ
    ТИПЗНАЧЕНИЯ(УчетНоменклатуры.Регистратор) КАК Документ
ИЗ
    РегистрНакопления.УчетНоменклатуры КАК УчетНоменклатуры

Функция ПРЕДСТАВЛЕНИЕ

Описание:

Данная функция предназначена для получения строкового представления значения произвольного типа.

Параметр функции – выражение любого типа.

Возвращаемое значение – представление значения, тип Строка.

Результат работы функции не может быть использован внутри других функций, за исключением функции ПРЕДСТАВЛЕНИЕ.

Пример:

ВЫБРАТЬ
    ПРЕДСТАВЛЕНИЕ(Документ.Контрагент) КАК Получатель,
    ПРЕДСТАВЛЕНИЕ(Документ.Дата) КАК Дата
ИЗ
    Документ.РасходнаяНакладная КАК Документ

Результат:

Рис. 334. Результат запроса

Функция ЕСТЬNULL (ISNULL)

Описание:

Функция предназначена для замены значения NULL на другое значение.

Параметры функции:

● первый параметр – выражение любого типа;

● второй параметр – выражение любого типа.

Возвращаемое значение: значение первого параметра, если первый параметр не содержит значение NULL; значение второго параметра в противном случае.

Второй параметр будет преобразован к типу первого в том случае, если тип первого параметра является строкой или числом.

Пример:

// Получить сумму по полю Количество. В случае, если нет 
// записей, получить 0
ВЫБРАТЬ
    ЕСТЬNULL(СУММА(РасходнаяНакладнаяСостав.Количество), 0)
ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав

В выражениях языка запросов могут применяться операции выбора, которые позволяют получить одно из возможных значений в соответствии с указанными условиями.

Операция выбора описывается следующим набором правил:

<Операция выбора>

ВЫБОР
<Альтернативы выбора>
[ИНАЧЕ <Выражение>]
КОНЕЦ

<Альтернативы выбора>

<Одиночный выбор>
[<Альтернативы выбора>]

<Одиночный выбор>

КОГДА <Логическое выражение>
ТОГДА <Выражение>

В операции выбора может указываться неограниченное количество альтернативных одиночных выборов КОГДА … ТОГДА. Они обрабатываются в запросе последовательно. Если логическое выражение имеет значение Истина, обработка операции выбора завершается. Результатом операции является значение выражения, указанного после слова ТОГДА. Описание логических выражений см. здесь.

Значение выражения, указанного после слова ИНАЧЕ, используется в качестве результата операции выбора в том случае, если во всех ранее указанных альтернативных одиночных выборах предикат имел значение Ложь.

Пример:

ВЫБРАТЬ
    Номенклатура.Наименование,
    ВЫБОР
        КОГДА Номенклатура.ЭтоГруппа = ИСТИНА
            ТОГДА "Это группа"
        КОГДА Номенклатура.ЗакупочнаяЦена > 1000
            ТОГДА "1000 -"
        КОГДА Номенклатура.ЗакупочнаяЦена > 100
            ТОГДА "100 – 1000"
        КОГДА Номенклатура.ЗакупочнаяЦена > 10
            ТОГДА "10 – 100"
        КОГДА Номенклатура.ЗакупочнаяЦена > 0
            ТОГДА "0 – 10"
        ИНАЧЕ "Не задана"
    КОНЕЦ КАК Цена
ИЗ
    Справочник.Номенклатура КАК Номенклатура

Результат:

Рис. 335. Результат запроса (фрагмент)

Поля исходных таблиц могут иметь составной тип. Для таких полей возникает необходимость привести значения поля к какому-либо определенному типу. В языке запросов предусмотрена возможность приведения типа. Ею можно пользоваться в списке полей выборки и в условии отбора в предложении ГДЕ.

<Приведение типа>

ВЫРАЗИТЬ ( <Выражение> КАК <Тип значения> )

<Тип значения>

Булево |
Число [(Длина[, Точность])]|
Строка [(Длина)]|
Дата |
<Имя таблицы>

<Длина>

Число

<Точность>

Число

Выражение приводится к одному из примитивных типов или к ссылочному типу данных. В последнем случае имя таблицы указывает на соответствующую таблицу информационной базы.

Если выражение содержит в составном типе требуемый тип значения, то приведение типа считается осуществимым, и для каждого значения указанного типа результатом будет это самое значение. Для значений других типов результатом приведения типа будет значение NULL.

Если выражение не содержит в составном типе требуемый тип значения, то выполнение данного запроса завершится ошибкой из-за принципиальной невозможности совершить приведение типов.

В выражениях языка запросов могут напрямую указываться значения типа Булево, Число, Строка или Дата. Также могут использоваться значения параметров запроса.

<Значение>

ИСТИНА |
ЛОЖЬ |
<Литерал типа ЧИСЛО> |
<Литерал типа СТРОКА> |
<Литерал типа ДАТА> |
<Литерал типа ТИП> |
<Имя параметра> |
НЕОПРЕДЕЛЕНО |
NULL

<Литерал типа Число>

<Целое число>[.<Целое число>]

<Литерал типа Строка>

"<Последовательность символов>"

<Литерал типа Дата>

ДАТАВРЕМЯ ( <Целое число>, <Целое число>, <Целое число>[,
            <Целое число>, <Целое число>, <Целое число>] )

Значения типа Булево, Число, Строка в языке запросов задаются так же, как и во встроенном языке.

Значения типа Дата задаются с помощью ключевого слова ДАТАВРЕМЯ, после которого в скобках последовательно указываются год, месяц, день, час, минута, секунда. Последние три указывать необязательно.

ВНИМАНИЕ! Максимальная дата, которую возможно задать при помощи литерала ДАТАВРЕМЯ – 31.12.3999 23:59:59.

В запрос могут передаваться параметры (см. описание объекта Запрос). Значения параметров могут использоваться в выражениях языка запросов. Для этого необходимо указать символ "&" и после него имя параметра.

<Литерал типа Тип>

ТИП(<Имя типа>)

<Имя типа> – имя примитивного типа либо имя таблицы, тип ссылки которой нужно получить. Результатом данной конструкции будет значение типа Тип для указанного типа.

Пример:

// Получение типа "Строка"
ТИП(Строка)
// Получение типа – ссылка на справочник "Номенклатура"
ТИП(Справочник.Номенклатура) 

Значения типа Тип в языке запросов можно использовать в операциях сравнения, упорядочивания, группировки.

Пример:

ВЫБРАТЬ
    ТИПЗНАЧЕНИЯ(Остатки.Регистратор)
ИЗ
    РегистрНакопления.УчетНоменклатуры КАК Остатки
ГДЕ
ТИПЗНАЧЕНИЯ(Остатки.Регистратор) = ТИП(Документ.РасходнаяНакладная)

Возможна передача значения типа Тип как параметр запроса.

Пример:

ВЫБРАТЬ
    ТИПЗНАЧЕНИЯ(Остатки.Регистратор)
ИЗ
    РегистрНакопления.УчетНоменклатуры КАК Остатки
ГДЕ
    ТИПЗНАЧЕНИЯ(Остатки.Регистратор) = &Тип

При сравнении значения типа Тип значения упорядочиваются в следующем порядке (первый тип считается самым малым):

● NULL,

● Неопределено,

● Булево,

● Число,

● Дата,

● Строка,

● ссылка на таблицу,

● другие типы.

В языке запросов используются условия отбора, в соответствии с которыми осуществляется отбор данных в предложениях ГДЕ, ИМЕЮЩИЕ и СОЕДИНЕНИЕ. Условия описываются по следующим правилам:

<Условие отбора>

<Логическое слагаемое> [ИЛИ <Логическое слагаемое>]

<Логическое слагаемое>

<Логический сомножитель> [И <Логический сомножитель>]

<Логический сомножитель>

НЕ <Логический сомножитель> |
( <Условие отбора> ) |
<Логическое выражение>

В простейшем случае условие является выражением, результат которого имеет значение логического типа. Логические выражения описаны в следующем разделе.

Условия могут определяться и как более сложные логические выражения, где фигурируют простые логические выражения, соединенные между собой с помощью логических операторов И, ИЛИ, НЕ.

Логические операторы имеют приоритет:

● Самый высокий приоритет имеет логический оператор НЕ.

● Следующим по приоритету является оператор И.

● Самый низкий приоритет у оператора ИЛИ.

● В условиях сначала вычисляются простые логические выражения, затем операции НЕ, затем операции И, в последнюю очередь – операции ИЛИ. Для того чтобы обеспечить другой порядок вычислений, можно использовать круглые скобки ().

В языке запросов в операциях выбора и в условиях отборов используются логические выражения.

<Логическое выражение>

<Выражение> |
(<Выражение> | <Логическое выражение>) <Операция сравнения> (<Выражение> | <Логическое выражение>) |
<Выражение> [НЕ] В [ИЕРАРХИИ] (<Список значений>) |
<Выражение> [НЕ] В [ИЕРАРХИИ](<Описание запроса>) |
<Выражение> [НЕ] МЕЖДУ <Выражение> И <Выражение> |
<Выражение> ЕСТЬ [НЕ] NULL |
<Выражение> ССЫЛКА <Имя таблицы> |
<Выражение> [НЕ] ПОДОБНО <Литерал типа СТРОКА>
            [СПЕЦСИМВОЛ <Литерал типа СТРОКА>]

<Операция сравнения>

> | < | = | >= | <= | <>

<Список значений>

<Выражение>[, <Выражение> [, …]]

Логическим выражением может быть:

● обычное выражение языка запросов, если его результат имеет логический тип;

● операция сравнения двух выражений языка запросов; выполняется в соответствии с правилами сравнения значений, описанными в разделе «Правила сравнения значений»;

● оператор проверки совпадения/несовпадения значения выражения с одним из перечисленных или со значениями, содержащимися в результате другого запроса;

● оператор проверки вхождения значения выражения в диапазон;

● оператор проверки значения выражения на NULL;

● оператор проверки ссылочного значения выражения на ссылку на определенную таблицу;

● оператор проверки строкового значения наподобие шаблону.

При сравнении значений используются правила сравнения значений, описанные ниже.

Поскольку в языке запросов могут сравниваться значения разных типов, определены правила, по которым выполняется сравнение двух значений.

Данные правила используются:

● для сравнения значений в операторах сравнения;

● для определения максимального и минимального значений в агрегатных функциях МИНИМУМ и МАКСИМУМ;

● для упорядочивания записей результата запроса в соответствии с порядком, заданным в предложении УПОРЯДОЧИТЬ ПО.

Если типы значений отличаются друг от друга, то отношения между значениями определяются на основании приоритета типов:

● тип NULL (самый низший);

● тип Булево;

● тип Число;

● тип Дата;

● тип Строка;

● ссылочные типы.

Отношения между различными ссылочными типами определяются на основе внутренних ссылочных номеров таблиц, соответствующих тому или иному типу.

Если типы данных совпадают, то производится сравнение значений по следующим правилам:

● У типа Булево значение Истина больше значения Ложь.

● У типа Число обычные правила сравнения для чисел.

● У типа Дата более ранние даты меньше более поздних.

● У типа Строка сравнение производится в соответствии с установленными национальными особенностями базы данных. Операция сравнения строк не учитывает концевые пробелы, в отличие от сравнения строк на встроенном языке, где концевые пробелы участвуют в операции сравнения. Например, сравнение строк «bb» и «bb " на встроенном языке вернет Ложь (строки не равны), и Истина в языке запросов (строки равны).

● Ссылочные типы сравниваются на основе своих значений (номера записи и т. п.).

● Не поддерживается сравнение полей типа УникальныйИдентификатор с полями других типов;

● Не допускается сравнение полей неограниченной длины (строки неограниченной длины, ХранилищеЗначения, поле ТипЗначения из таблицы планов видов характеристик).

ВНИМАНИЕ! Любая операция сравнения двух значений, в которой участвует хотя бы одно значение NULL, дает результат, аналогичный значению Ложь.

Форма оператора «В» для проверки совпадения с одним из перечисленных

Оператор В позволяет проверить, совпадает ли значение выражения, указанного справа от него, с одним из значений, описанных слева. Если совпадает хотя бы с одним, результатом оператора будет Истина, иначе – Ложь. Применение НЕ изменяет действие оператора на обратное. Сравнение значений производится по правилам, описанным в разделе «Правила сравнения значений».

Пример:

ВЫБРАТЬ
    Номенклатура.Наименование
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Родитель.Наименование В
                        ("Бытовая техника", "Оргтехника")

Форма оператора «В» для проверки принадлежности по иерархии

Для справочников проверка может осуществляться и на принадлежность по иерархии. Результатом оператора В ИЕРАРХИИ будет Истина, если значение выражения слева является ссылкой на элемент справочника и входит во множество значений справа или иерархически принадлежит какой-нибудь группе, содержащейся в этом множестве.

Пример:

// В качестве параметра Группа в запрос передается ссылка
// на какую-либо группу справочника Номенклатура.
ВЫБРАТЬ
    Номенклатура.Наименование
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка В ИЕРАРХИИ(&Группа)

В качестве множества значений, на совпадение с которыми выполняется проверка, может фигурировать и результат запроса. В этом случае справа от оператора В необходимо указать описание запроса.

Пример:

ВЫБРАТЬ
    Номенклатура.Наименование
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка В ИЕРАРХИИ
            (ВЫБРАТЬ
                Номенклатура.Ссылка
            ИЗ
                Справочник.Номенклатура КАК Номенклатура
            ГДЕ
                Номенклатура.Наименование = "Одежда")

Форма оператора «В» для проверки совпадения значения с одним из результатов запроса

Пример применения данного оператора:

Пример:

// Выбрать названия товаров, которые присутствовали
// в расходных накладных 
ВЫБРАТЬ
    Товары.Наименование
ИЗ
    Справочник.Номенклатура КАК Товары
ГДЕ
    Товары.Ссылка В
        (ВЫБРАТЬ
            РасходнаяНакладнаяСостав.Номенклатура
        ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав)

Результат:

Рис. 336. Результат запроса (фрагмент)

Для получения противоположного результата, то есть если нужно определить, что значение не совпадает ни с одним из результатов запроса, запрос выглядит следующим образом.

Пример:

// Выбрать названия товаров, которые присутствовали
// в расходных накладных 
ВЫБРАТЬ
    Товары.Наименование
ИЗ
    Справочник.Номенклатура КАК Товары
ГДЕ
    (НЕ Товары.Ссылка В
        (ВЫБРАТЬ
            РасходнаяНакладнаяСостав.Номенклатура
        ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав))

Результат:

Рис. 337. Результат запроса (фрагмент)

Заметим, что из запроса операции В возможно обращение к полям таблиц, которые встречались во внешнем запросе до появления операции.

Пример:

// Выбрать названия товаров, которые присутствовали
// в расходных накладных
ВЫБРАТЬ
    Товары.Наименование
ИЗ
    Справочник.Номенклатура КАК Товары
ГДЕ
    Товары.Ссылка В
    (ВЫБРАТЬ
        РасходнаяНакладнаяСостав.Номенклатура
    ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав
    ГДЕ
        РасходнаяНакладнаяСостав.Номенклатура = Товары.Ссылка)

Результат:

Рис. 338. Результат запроса (фрагмент)

Использование операции «В» («НЕ В») по нескольким полям

Синтаксис для вложенного запроса:

(выражение1, выражение2, …, выражениеN) В (ВЫБРАТЬ выражение1, выражение2, …, выражениеN …)

Синтаксис для таблицы значений:

(выражение1, выражение2, …, выражениеN) В (&Параметр)

В качестве параметра следует передавать таблицу значений, первые N колонок которой будут использоваться для операции В.

Оператор МЕЖДУ позволяет проверить, входит ли значение выражения, указанного слева от него, в диапазон, указанный справа. Если входит, результатом оператора будет Истина, иначе – Ложь. Применение НЕ изменяет действие оператора на обратное. Правила сравнения значений см. здесь.

Пример:

ВЫБРАТЬ
    Номенклатура.Наименование,
    Номенклатура.ЗакупочнаяЦена
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.ЗакупочнаяЦена МЕЖДУ 100 И 1000

Оператор ЕСТЬNULL позволяет проверить значение выражения слева от него на NULL. Если значение равно NULL, результатом оператора будет Истина, иначе – Ложь. Применение НЕ изменяет действие оператора на обратное.

Пример:

ВЫБРАТЬ
    Номенклатура.Наименование,
    Номенклатура.ЗакупочнаяЦена
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.ЗакупочнаяЦена ЕСТЬ NULL

Оператор ССЫЛКА позволяет проверить, является ли значение выражения, указанного слева от него, ссылкой на таблицу, указанную справа. Если да, результатом оператора будет Истина, иначе – Ложь. Описание разыменования таблиц см. здесь.

Пример:

ВЫБРАТЬ
    РасходнаяНакладная.Номер,
    РасходнаяНакладная.Дата
ИЗ
    Документ.РасходнаяНакладная КАК РасходнаяНакладная
ГДЕ
    РасходнаяНакладная.Контрагент ССЫЛКА Справочник.Контрагенты

Оператор ПОДОБНО позволяет сравнить значение выражения, указанного слева от него, со строкой шаблона, указанной справа. Значение выражения должно иметь тип Строка. Если значение выражения удовлетворяет шаблону, результатом оператора будет Истина, иначе – Ложь.

Следующие символы в строке шаблона являются служебными и имеют смысл, отличный от символа строки:

● «%» (процент): последовательность, содержащая любое количество произвольных символов.

● «_» (подчеркивание): один произвольный символ.

● «[…]» (в квадратных скобках один или несколько символов): любой одиночный символ из перечисленных внутри квадратных скобок. В перечислении могут встречаться диапазоны, например, a-z, означающие произвольный символ, входящий в диапазон, включая концы диапазона.

● «[^…]» (в квадратных скобках значок отрицания, за которым следует один или несколько символов): любой одиночный символ, кроме тех, которые перечислены следом за значком отрицания.

Любой другой символ означает сам себя и не несет никакой дополнительной нагрузки.

Если в качестве самого себя необходимо записать один из перечисленных символов, то ему должен предшествовать спецсимвол. Сам спецсимвол (любой подходящий символ) определяется в этом же операторе после ключевого слова СПЕЦСИМВОЛ.

Например, шаблон:

"%АБВ[0-9][абвг]\_абв%" СПЕЦСИМВОЛ "\"

означает подстроку, состоящую из последовательности символов:

● буквы А,

● буквы Б,

● буквы В,

● одной цифры,

● одной из букв а, б, в или г,

● символа подчеркивания,

● буквы а,

● буквы б,

● буквы в.

Причем перед этой последовательностью может располагаться произвольный набор символов.

При работе в клиент-серверном варианте, возможна ситуация, когда запрос, содержащий оператор ПОДОБНО, не может быть выполнен. Такая ситуация возможна в том случае, когда длина строки с шаблоном превышает некоторое число, зависимое от используемой СУБД.

Для формирования запросов, выборки и обработки результатов запросов в языке предусмотрен специальный набор объектов. С помощью этих объектов выполняется формирование запроса, обход записей запроса и т. д.

Основные приемы работы с запросами во встроенном языке системы «1С:Предприятие» удобнее всего рассматривать на примерах. Приведем типичный пример использования запроса.

// Создадим Запрос
Запрос = Новый Запрос("ВЫБРАТЬ Товар.Наименование Наименование,
    |Товар.Родитель.Наименование НаименованиеРодителя
    |ИЗ Справочник.Товары Товар");
// Выполним запрос и запишем результат в переменную РезультатЗапроса.
РезультатЗапроса = Запрос.Выполнить();
// Получим выборку из результата запроса.
Выборка = РезультатЗапроса.Выбрать();
// Пока в выборке есть записи ...
Пока Выборка.Следующий() Цикл
    // ... выведем в окно сообщений поля из результата.
    Товар = Выборка.Наименование;
    Родитель = Выборка.НаименованиеРодителя;
    Сообщить("Товар: " + Товар + " Родитель: " + Родитель);
КонецЦикла;

Как видно из этого примера, работа с запросом ведется при помощи трех основных объектов:

● Запрос – объект, выполняющий сам запрос. Представлен в примере переменной с именем Запрос.

● РезультатЗапроса – объект, содержащий полученные при выполнении запроса данные. Представлен в примере переменной с именем РезультатЗапроса.

● ВыборкаИзРезультатаЗапроса – объект, позволяющий обходить (т. е. перебирать) записи из результата. Представлен в примере переменной с именем Выборка.

Рассмотрим подробнее объект ВыборкаИзРезультатаЗапроса.

Для этого нам понадобится следующий запрос:

ВЫБРАТЬ
    РасходнаяНакладнаяСостав.Номенклатура КАК Номенклатура,
    РасходнаяНакладнаяСостав.Количество КАК Количество
ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав
УПОРЯДОЧИТЬ ПО
    РасходнаяНакладнаяСостав.Номенклатура
ИТОГИ
    СУММА(Количество)
ПО
    Номенклатура ИЕРАРХИЯ

Результат:

Рис. 339. Рабочая выборка

В этой таблице мы добавили первый столбец, которого нет в тексте запроса, но он будет использоваться нами в дальнейшем для идентификации записи в результате. Итоговые записи в таблице выделены курсивом, итоговые записи для иерархических уровней справочника выделены жирным шрифтом.

Первый и самый простой способ обхода – линейный. При линейном обходе выборка будет выдавать записи в той последовательности, в которой они располагаются в результате запроса. В нашем примере это будут записи с номерами 1, 2, 3, 4, 5 и так далее до записи с номером 20.

Для получения линейной выборки из результата необходимо вызвать метод Выбрать() объекта РезультатЗапроса без параметров либо с параметром ОбходРезультатаЗапроса.Прямой.

Пример:

СпособВыборки = ОбходРезультатаЗапроса.Прямой;
Выборка1 = РезультатЗапроса.Выбрать(СпособВыборки);
// что равнозначно записи
Выборка1 = РезультатЗапроса.Выбрать();

Следующий способ обхода результата – иерархический. При данном способе обходятся только записи, находящиеся на одном уровне. Для получения иерархической выборки из результата необходимо вызвать метод Выбрать() объекта РезультатЗапроса с параметром ОбходРезультатаЗапроса.ПоГруппировкамСИерархией.

Пример:

СпособВыборки = ОбходРезультатаЗапроса.ПоГруппировкамСИерархией;
Выборка2 = РезультатЗапроса.Выбрать(СпособВыборки);

Выборка из результата с иерархическим обходом в нашем примере обойдет только записи с номерами 1 и 11, так как только эти две записи находятся на самом верхнем уровне. Проиллюстрируем это, представив наш результат в виде дерева, где узлами будут итоговые записи, а листьями дерева – детальные записи. Вот что у нас получится:

Рис. 340. Иерархический обход результата запроса

Из этого рисунка видно, что именно записи с номерами 1 и 11 и только они находятся на первом уровне дерева, в результате чего только они и попадают в первый проход иерархической выборки.

Возникает вопрос: как получать остальные записи результата запроса? Для этого у объекта ВыборкаИзРезультатаЗапроса можно получить еще одну выборку, которая будет обходить подчиненные записи текущей записи выборки. В нашем примере в момент, когда объект Выборка2 будет позиционирован на запись с номером 1, мы запросим у него иерархическую выборку. Таким образом мы получим выборку, которая нам вернет записи с номерами 2, 7. А когда Выборка2 будет позиционирована на записи с номером 11, полученная у нее иерархическая выборка вернет записи с номерами 12, 16. Так реализуется иерархический обход результатов запроса.

Заметим, что у выборки можно получать вложенные выборки любого типа. Так, если бы мы запросили у объекта Выборка2, позиционированной на записи 1, линейную выборку, то с ее помощью мы бы получили записи с номерами со 2-го по 10-й. Проиллюстрируем описанную методику на примере.

Пример:

Процедура ВыполнитьЗапрос()
    // Создадим запрос.
    Запрос = Новый Запрос;
    // Установим текст запроса
    Запрос.Текст = "ВЫБРАТЬ
    |РасходнаяНакладнаяСостав.Номенклатура КАК Номенклатура,
    |РасходнаяНакладнаяСостав.Количество КАК Количество
    |ИЗ
    |Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав
    |УПОРЯДОЧИТЬ ПО
    |РасходнаяНакладнаяСостав.Номенклатура
    |ИТОГИ
    |СУММА(Количество)
    |ПО
    |Номенклатура ИЕРАРХИЯ";
    // Выполним запрос и запишем результат в переменную
    // РезультатЗапроса.
 РезультатЗапроса = Запрос.Выполнить();
    // Получим выборку из результата запроса.
    СпособВыборки = ОбходРезультатаЗапроса.ПоГруппировкамСИерархией;
    Выборка = РезультатЗапроса.Выбрать(СпособВыборки);
    ВыдатьРекурсивно(Выборка);
КонецПроцедуры
Процедура ВыдатьРекурсивно(Выборка)
    // Пока в выборке есть записи ...
    Пока Выборка.Следующий() Цикл
        // ... выведем в окно сообщений поля из результата
        Товар = Выборка.Наименование;
        Количество = Выборка.Количество;
        Сообщить("Товар: " + Товар + " Количество: " + Количество);
        // Продолжим выборку подчиненных записей
        СпособВыборки = ОбходРезультатаЗапроса.ПоГруппировкамСИерархией;
        ВыдатьРекурсивно(Выборка.Выбрать(СпособВыборки, Выборка.Группировка()));
    КонецЦикла;
КонецПроцедуры

Третий и последний способ обхода результата – по группам. Он сходен с иерархическим обходом, но с одним различием: записи с иерархическими итогами при обходе в нем рассматриваются как детальные, а не как итоговые. Для получения выборки по группам из результата запроса необходимо вызвать метод Выбрать() объекта РезультатЗапроса с параметром ОбходРезультатаЗапроса.ПоГруппировкам.

Пример:

СпособВыборки = ОбходРезультатаЗапроса.ПоГруппировкам;
Выборка2 = РезультатЗапроса.Выбрать(СпособВыборки);
// Перебрав в ней все, мы получим записи с номерами:
// 1, 2, 7, 11, 12, 16.

Пример:

Процедура ВыполнитьЗапрос()
    // Создадим запрос.
    Запрос = Новый Запрос;
    // Установим текст запроса
    Запрос.Текст = "ВЫБРАТЬ
    |РасходнаяНакладнаяСостав.Номенклатура КАК Номенклатура,
    |РасходнаяНакладнаяСостав.Количество КАК Количество
    |ИЗ
    |Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав
    |УПОРЯДОЧИТЬ ПО
    |РасходнаяНакладнаяСостав.Номенклатура
    |ИТОГИ
    |СУММА(Количество)
    |ПО
    |Номенклатура ИЕРАРХИЯ";
    // Выполним запрос и запишем результат в переменную
    // РезультатЗапроса.
    РезультатЗапроса = Запрос.Выполнить();
    // Получим выборку из результата запроса
    СпособВыборки = ОбходРезультатаЗапроса.ПоГруппировкам;
    Выборка = РезультатЗапроса.Выбрать(СпособВыборки);
    // Пока в выборке есть записи ...
    Пока Выборка.Следующий() Цикл
        // ... выведем в окно сообщений поля из результата
        Товар = Выборка.Наименование;
        Количество = Выборка.Количество;
        Сообщить("Товар: "+Товар+" Итого по товару: "+Количество);
        ВыдатьДочерниеЗаписи(Выборка.Выбрать());
    КонецЦикла;
КонецПроцедуры
Процедура ВыдатьДочерниеЗаписи (Выборка)
    // Пока в выборке есть записи ...
    Пока Выборка.Следующий() Цикл
        // ... выведем в окно сообщений поля из результата
        Товар = Выборка.Наименование;
        Количество = Выборка.Количество;
        Сообщить("Товар: "+Товар+" Количество: "+Количество);
    КонецЦикла;
КонецПроцедуры

Объект ВыборкаИзРезультатаЗапроса предназначен для обхода записей результата запроса. Можно представить себе выборку как некоторый объект, который содержит указатель на текущую запись в результате и предоставляет программе доступ ко всем полям текущей записи. Для навигации по записям запроса используются три метода:

● Следующий() – перейти к следующей записи результата в соответствии с порядком обхода выборки. При первом вызове позиционирует выборку на первую запись. Когда будут выбраны все записи, данный метод просигнализирует об этом, вернув значение Ложь.

● СледующийПоЗначениюПоля() – получить следующую запись со значением в заданном поле, отличающимся от значения в этом поле текущей записи.

● НайтиСледующий() – найти запись с заданными значениями некоторых полей.

Метод позволяет сгруппировать записи результата по значениям полей.

Пример:

ВЫБРАТЬ
    Док.Номенклатура,
    Док.Ссылка.Контрагент КАК Контрагент,
    Док.Количество
ИЗ
    Документ.РасходнаяНакладная.Состав КАК Док
УПОРЯДОЧИТЬ ПО
    Док.Номенклатура.Наименование,
    Контрагент

Результат:

Рис. 341. Результат запроса

Получим линейную выборку из результата запроса и обойдем выборку при помощи метода СледующийПоЗначениюПоля().

Пример:

Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.СледующийПоЗначениюПоля("Товар") Цикл
    // здесь мы получим записи с номерами 1, 5, 9, 12
    Пока Выборка.СледующийПоЗначениюПоля("Контрагент") Цикл
        // здесь мы сначала получим записи с номерами 1, 2, 3, 4
        // затем 5, 6, 7
        // затем 9, 10, 11
        // затем 12, 13, 14, 15
    КонецЦикла;
КонецЦикла;

Следует обратить внимание на то, что во внутреннем цикле не была выбрана запись с номером 8, т. к. в ней такое же значение поля Получатель, как и в предыдущей записи.

Заметим, что если в цикле получения по значению поля получать записи при помощи метода Следующий(), то будут выбраны все записи с равным значением поля, заданного в предыдущем вызове метода СледующийПоЗначениюПоля().

Пример:

Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.СледующийПоЗначениюПоля("Товар") Цикл
    // здесь мы получим записи с номерами 1, 5, 9, 12
    Пока Выборка.Следующий() Цикл
        // здесь мы сначала получим записи с номерами 1, 2, 3, 4
        // затем 5, 6, 7, 8
        // затем 9, 10, 11
        // затем 12, 13, 14, 15
    КонецЦикла;
КонецЦикла;

В тот момент, когда выборка позиционирована на записи, мы можем у выборки узнать характеристики этой записи. Получение характеристик записи осуществляется следующими методами:

● Уровень() – определяет уровень записи в результате запроса.

● ТипЗаписи() – определяет принадлежность записи к одному из следующих типов:

● групповой итог,

● итого по иерархии,

● детальная запись,

● общий итог.

● Группировка() – определяет имя поля, по которому были рассчитаны итоги.

Для иллюстрации работы этих методов посмотрим, что они будут возвращать в виде записей для запроса, рассматриваемого в начале главы.

ВЫБРАТЬ
    РасходнаяНакладнаяСостав.Номенклатура КАК Номенклатура,
    РасходнаяНакладнаяСостав.Количество КАК Количество
ИЗ
    Документ.РасходнаяНакладная.Состав КАК РасходнаяНакладнаяСостав
УПОРЯДОЧИТЬ ПО
    РасходнаяНакладнаяСостав.Номенклатура
ИТОГИ
    СУММА(Количество)
ПО
    Номенклатура ИЕРАРХИЯ

Результат:

Рис. 342. Иерархия записей

Язык запросов системы «1С:Предприятие» позволяет использовать временные таблицы в запросах. Использование временных таблиц помогает повысить производительность запросов и сделать текст сложных запросов более легким для восприятия.

Работа с временными таблицами обеспечивается двумя составляющими:

● объектом встроенного языка МенеджерВременныхТаблиц, который хранит в себе данные временных таблиц;

● синтаксисом языка запросов, позволяющим создавать новые временные таблицы и использовать существующие временные таблицы.

Менеджер временных таблиц предназначен для управления временем существования временных таблиц, создаваемых в процессе работы прикладного решения.

В одном прикладном решении может быть создано произвольное количество экземпляров менеджера временных таблиц, каждый из которых хранит свой набор временных таблиц. Каждая временная таблица однозначно идентифицируется своим именем, и в пределах одного менеджера временных таблиц все временные таблицы должны иметь уникальные имена.

Примечание. Имена временных таблиц должны соответствовать требованиям, предъявляемым к именам переменных встроенного языка (см. здесь).

Экземпляр менеджера временных таблиц может быть создан с помощью конструктора Новый.

Пример:

МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;

Все временные таблицы, созданные в данном экземпляре менеджера, существуют до тех пор, пока существует сам экземпляр менеджера временных таблиц. При уничтожении экземпляра менеджера все временные таблицы, которые содержатся в нем, также удаляются.

Менеджер временных таблиц можно закрыть принудительно при помощи метода Закрыть(). При этом будут удалены все созданные в нем таблицы. Дальнейшая работа с данным экземпляром менеджера будет невозможна.

Создание временных таблиц осуществляется с помощью объекта Запрос встроенного языка системы «1С:Предприятие».

Связь запроса с менеджером временных таблиц осуществляется с помощью свойства МенеджерВременныхТаблиц запроса, в котором указывается тот экземпляр менеджера, в котором должны создаваться временные таблицы.

Пример:

МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = МенеджерВременныхТаблиц;

Временная таблица может быть создана на основе данных базы данных или на основе внешнего источника данных (например, таблицы значений).

Для того чтобы создать временную таблицу на основе данных базы данных, следует установить объекту Запрос менеджер временных таблиц, а затем выполнить запрос к базе данных, используя ключевое слово ПОМЕСТИТЬ, после которого указать имя создаваемой временной таблицы. Ключевое слово ПОМЕСТИТЬ располагается после списка выборки запроса.

Пример:

ВЫБРАТЬ
    Номенклатура.Код,
    Номенклатура.Наименование
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
    Справочник.Номенклатура КАК Номенклатура

Результат исполнения такого запроса будет содержать одну строку с одной колонкой Количество, в которой будут находиться записи, помещенные в созданную таблицу.

Если менеджер временных таблиц не установлен или был закрыт, или в установленном менеджере временных таблиц уже существует таблица с указанным именем, будет выдана ошибка.

При создании временных таблиц не на основании внешнего источника можно использовать конструкцию ДЛЯ ИЗМЕНЕНИЯ. Это необходимо в тех случаях, когда требуется получить данные во временную таблицу и одновременно заблокировать их от чтения другими транзакциями.

Пример:

ВЫБРАТЬ
    РасходнаяНакладная.Ссылка,
    РасходнаяНакладная.Номер,
    РасходнаяНакладная.Дата
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
    Документ.РасходнаяНакладная КАК РасходнаяНакладная
ГДЕ
    РасходнаяНакладная.Ссылка В(&Документы)
ДЛЯ ИЗМЕНЕНИЯ

При необходимости создания индекса для временной таблицы следует в запросе указать ключевое слово ИДЕКСИРОВАТЬ ПО, после которого перечислить поля, по которым нужно построить индекс.

Пример:

ВЫБРАТЬ
    Номенклатура.Код КАК Код,
    Номенклатура.Наименование
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ИНДЕКСИРОВАТЬ ПО
    Код

Поля, по которым происходит индексирование, должны находиться в списке выборки.

Если в качестве источника используется таблица значений, то у этой таблицы значений должны быть явно указаны типы значений, содержащихся в колонках.

Для того чтобы создать временную таблицу и заблокировать данные таблиц, на основании которых создается временная таблица, следует использовать конструкцию ДЛЯ ИЗМЕНЕНИЯ.

Пример:

ВЫБРАТЬ
    РасходнаяНакладная.Ссылка,
    РасходнаяНакладная.Номер,
    РасходнаяНакладная.Дата
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
    Документ.РасходнаяНакладная КАК РасходнаяНакладная
ГДЕ
    РасходнаяНакладная.Ссылка В(&Документы)
ДЛЯ ИЗМЕНЕНИЯ

Для того чтобы создать временную таблицу на основании внешнего источника, следует в тексте запроса в списке источников указать имя параметра, в который будет помещен внешний источник. Остальной синтаксис идентичен обычному созданию временной таблицы.

В качестве внешнего источника могут выступать:

● таблица значений,

● табличная часть,

● результат запроса.

Ниже приведен пример создания временной таблицы на основе внешнего источника:

ВЫБРАТЬ
    Источник.Код,
    Источник.Наименование
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
    &ВнешнийИсточник КАК Источник

В данном примере во временную таблицу ВременнаяТаблица будет помещено содержимое колонок Код и Наименование из внешнего источника, например, таблицы значений, переданной в качестве параметра ВнешнийИсточник.

Внимание! Создание временной таблицы невозможно, если она создается на основании таблицы значений, которая содержит в колонках значение типа УникальныйИдентификатор.

ВНИМАНИЕ! Если временная таблица создается на основании внешнего источника, в запросе нельзя использовать объединения и соединения, а также поля, являющиеся реквизитами полей таблиц, на основании которых создается временная таблица.

Для использования существующих временных таблиц в запросе следует установить объекту Запрос менеджер временных таблиц, после чего к временным таблицам, содержащимся в данном менеджере временных таблиц, можно обращаться по имени, как к обычным таблицам запроса.

Для удаления временной таблицы из менеджера временных таблиц следует воспользоваться ключевым словом языка запроса УНИЧТОЖИТЬ, после которого указывается имя уничтожаемой таблицы, например:

УНИЧТОЖИТЬ ВременнаяТаблица

Если уничтожаемой таблицы не существует, будет выдана ошибка.

При отладке сложных запросов, содержащих временные таблицы, возникает необходимость анализировать их (временных таблиц) содержимое. Для этого можно воспользоваться методом Запрос.ВыполнитьПакетСПромежуточнымиДанными() (см. здесь) или воспользоваться менеджером временных таблиц.

Примечание. Рассматриваемый запрос не имеет никакого прикладного смысла, а служит исключительно для демонстрации работы с временными таблицами в процессе отладки запроса.

Рассмотрим пример такого использования:

Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.Текст = "ВЫБРАТЬ
    |    ПродажиОбороты.Товар,
    |    ПродажиОбороты.КоличествоОборот
    |ПОМЕСТИТЬ Продажи
    |ИЗ
    |    РегистрНакопления.Продажи.Обороты КАК ПродажиОбороты
    |;
    |
    |/////////////////////////////////
    |ВЫБРАТЬ
    |    ТоварныеЗапасыОстатки.Товар,
    |    ТоварныеЗапасыОстатки.КоличествоОстаток
    |ПОМЕСТИТЬ Остатки
    |ИЗ
    |    РегистрНакопления.ТоварныеЗапасы.Остатки КАК ТоварныеЗапасыОстатки
    |;
    |
    |/////////////////////////////////
    |ВЫБРАТЬ
    |    Товары.Наименование,
    |    Продажи.КоличествоОборот,
    |    Остатки.КоличествоОстаток
    |ИЗ
    |    Справочник.Товары КАК Товары
    |        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Остатки КАК Остатки
    |        ПО Товары.Ссылка = Остатки.Товар
    |        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Продажи КАК Продажи
    |        ПО Товары.Ссылка = Продажи.Товар";
Результат = Запрос.Выполнить();
ПродажиТЗ = Запрос.МенеджерВременныхТаблиц.Таблицы["Продажи"].ПолучитьДанные().Выгрузить();
ОстаткиТЗ = Запрос.МенеджерВременныхТаблиц.Таблицы[1].ПолучитьДанные().Выгрузить();

В результате выполнения запроса свойство Запрос.МенеджерВременныхТаблиц.Таблицы станет содержать коллекцию временных таблиц, содержащих результаты исполнения пакетного запроса. С помощью свойства Таблицы можно получать доступ к каждой временной таблице, причем делать это можно как по имени, так и по индексу временной таблицы. Например, таблица значений ПродажиТЗ получена в результате доступа к временной таблице Продажи по имени (Таблицы["Продажи"]). В то же время формирование таблицы значений ОстаткиТЗ осуществляется доступом по индексу (Таблицы[1]).

Платформа системы «1С:Предприятие» позволяет работать с пакетами запросов. В пакетном запросе тексты запросов разделяются символом «;». Запросы исполняются последовательно, при этом временные таблицы, которые были созданы во время исполнения какого-либо запроса, будут существовать до окончания исполнения всего пакета запроса или до исполнения в пакете запроса, уничтожающего данную временную таблицу, например:

Запрос = Новый Запрос;
Запрос.Текст =
    "ВЫБРАТЬ
    |УчетНоменклатурыОстаткиИОбороты.Номенклатура,
    |УчетНоменклатурыОстаткиИОбороты.КоличествоПриход,
    |УчетНоменклатурыОстаткиИОбороты.КоличествоРасход,
    |УчетНоменклатурыОстаткиИОбороты.КоличествоКонечныйОстаток
    |ПОМЕСТИТЬ УчетНоменклатуры
    |ИЗ
    |РегистрНакопления.УчетНоменклатуры.ОстаткиИОбороты(, , Авто, , ) КАК УчетНоменклатурыОстаткиИОбороты
    |;
    |
    |ВЫБРАТЬ
    |УчетНоменклатуры.Номенклатура,
    |УчетНоменклатуры.КоличествоРасход,
    |УчетНоменклатуры.КоличествоКонечныйОстаток
    |ИЗ
    |УчетНоменклатуры КАК УчетНоменклатуры
    |";
Результат = Запрос.Выполнить();

Первый запрос создает временную таблицу, данные из которой используются во втором запросе.

Если объекту Запрос, исполняющему пакетный запрос, установлен менеджер временных таблиц, временные таблицы, которые не были уничтожены в рамках пакетного запроса, сохранятся в установленном менеджере. В тексте пакетного запроса возможно использование и уничтожение временных таблиц, которые существовали в установленном менеджере временных таблиц на момент запуска пакета на исполнение.

Кроме метода Выполнить(), последовательно выполняющего все запросы пакета и возвращающего результат последнего запроса в пакете, платформа системы «1С:Предприятие» предоставляет еще один метод – ВыполнитьПакет(). Этот метод последовательно выполняет все запросы и возвращает массив результатов для каждого запроса из пакета в последовательности расположения запросов в тексте пакета. Результатом выполнения запроса на уничтожение временной таблицы является значение Неопределено, которое также помещается в массив результатов.

При необходимости отладки пакетного запроса, предоставляется возможность использовать метод ВыполнитьПакетСПромежуточнымиДанными(). В этом случае результат работы аналогичен методу ВыполнитьПакет() с тем отличием, что временные таблицы, сформированные в пакете запросов, будут представлены не количеством записей в соответствующей временной таблице (как при вызове метода ВыполнитьПакет()), а содержимым этой временной таблицы.

Таким образом, можно переработать вышеприведенный пример следующим образом:

Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |УчетНоменклатурыОстаткиИОбороты.Номенклатура,
    |УчетНоменклатурыОстаткиИОбороты.КоличествоПриход,
    |УчетНоменклатурыОстаткиИОбороты.КоличествоРасход,
    |УчетНоменклатурыОстаткиИОбороты.КоличествоКонечныйОстаток
    |ПОМЕСТИТЬ УчетНоменклатуры
    |ИЗ
    |РегистрНакопления.УчетНоменклатуры.ОстаткиИОбороты(, , Авто, , ) КАК УчетНоменклатурыОстаткиИОбороты
    |;
    |
    |ВЫБРАТЬ
    |УчетНоменклатуры.Номенклатура,
    |УчетНоменклатуры.КоличествоРасход,
    |УчетНоменклатуры.КоличествоКонечныйОстаток
    |ИЗ
    |УчетНоменклатуры КАК УчетНоменклатуры
    |";
РезультатПакета = Запрос.ВыполнитьПакетСПромежуточнымиДанными();
ВременнаяТаблица = РезультатПакета[0].Выгрузить();
Результат = РезультатПакета[1].Выгрузить();

В этом случае в переменной ВременнаяТаблица будет доступна таблица значений, которая содержит данные временной таблицы УчетНоменклатуры, а в переменной Результат будет доступна временная таблица с результатом работы последнего запроса пакета (фактический результат работы пакета).

Система «1С:Предприятие» предоставляет возможность формировать или модифицировать запрос из встроенного языка. Для этого существует специальный объект СхемаЗапроса. С помощью свойства ПакетЗапросов разработчик получает доступ ко всем запросам, которые в данный момент редактируются с помощью объекта СхемаЗапроса. Методы УстановитьТекстЗапроса() предназначен для заполнения объекта СхемаЗапроса на основании переданного текста запроса, а метод ПолучитьТекстЗапроса() служит для обратной операции.

В качестве примера использования объектной модели работы с запросами будет приведен программный код, необходимый для формирования следующего запроса:

ВЫБРАТЬ
    ТоварныеЗапасыОстатки.Товар КАК Товар,
    ТоварныеЗапасыОстатки.Склад КАК Склад,
    ТоварныеЗапасыОстатки.КоличествоОстаток КАК КоличествоОстаток,
    ВЫБОР
        КОГДА ТоварныеЗапасыОстатки.КоличествоОстаток > 0
            ТОГДА "Достаточно"
        ИНАЧЕ "Недостаточно"
    КОНЕЦ КАК Состояние
ИЗ
    РегистрНакопления.ТоварныеЗапасы.Остатки КАК ТоварныеЗапасыОстатки
ГДЕ
    ТоварныеЗапасыОстатки.Склад = &Склад
УПОРЯДОЧИТЬ ПО
    Товар,
    Склад,
    КоличествоОстаток
ИТОГИ
    СУММА(КоличествоОстаток)
ПО
    ОБЩИЕ,
    Товар,
    Склад

Код на встроенном языке:

Конструктор = Новый СхемаЗапроса;
Пакет1 = Конструктор.ПакетЗапросов[0];
Оператор1 = Пакет1.Операторы[0];
// добавим источник в запрос
ТаблицаРегистра = Оператор1.Источники.Добавить("РегистрНакопления.ТоварныеЗапасы.Остатки", "ТоварныеЗапасыОстатки");
// добавим поля в запрос
ПолеТовар = Оператор1.ВыбираемыеПоля.Добавить("ТоварныеЗапасыОстатки.Товар", 0);
ДоступноеПолеСклад = ТаблицаРегистра.Источник.ДоступныеПоля.Найти("Склад");
ПолеСклад = Оператор1.ВыбираемыеПоля.Добавить(ДоступноеПолеСклад, 1);
ПолеКоличество = Оператор1.ВыбираемыеПоля.Добавить("КоличествоОстаток", 2);
ВыражениеВыбора = Новый ВыражениеСхемыЗапроса("ВЫБОР
                |    КОГДА ТоварныеЗапасыОстатки.КоличествоОстаток > 0
                |        ТОГДА ""Достаточно""
                |    ИНАЧЕ ""Недостаточно""
                |КОНЕЦ");
ПолеВыбора = Оператор1.ВыбираемыеПоля.Добавить(Строка(ВыражениеВыбора), 3);
// зададим псевдонимы
Результат = Пакет1.Колонки.Найти(ПолеКоличество);
Результат.Псевдоним = "КоличествоОстаток";
Результат = Пакет1.Колонки[1];
Результат.Псевдоним = "Склад";
Результат = Пакет1.Колонки.Найти(ВыражениеВыбора);
Результат.Псевдоним = "Состояние";
// добавим условие
Оператор1.Отбор.Добавить(Строка(ПолеСклад) + " = &Склад");
// добавим упорядочивание
Пакет1.Порядок.Добавить(ПолеТовар);
Пакет1.Порядок.Добавить("Склад");
Пакет1.Порядок.Добавить("КоличествоОстаток");
// зададим контрольные точки итогов
Пакет1.ОбщиеИтоги = Истина;
Пакет1.КонтрольныеТочкиИтогов.Добавить(ПолеТовар);
Пакет1.КонтрольныеТочкиИтогов.Добавить("Склад");
// зададим агрегатные функции для итогов
КолонкаКоличество = Пакет1.Колонки.Найти(ПолеКоличество);
Пакет1.ВыраженияИтогов.Добавить("Сумма(" + КолонкаКоличество.Псевдоним +")");
Текст = Конструктор.ПолучитьТекстЗапроса();